SQL database de-duplication and SQL database

SQL database de-duplication and SQL database

For SQL deduplication, I would like to briefly introduce myself. If you have any suggestions or do not understand it, please give it a little more.

There are two common ways to remove SQL deduplication: DISTINCT and ROW_NUMBER (). Of course, ROW_NUMBER () has many important functions besides deduplication, I will give you a brief description of what I actually use.

Assume that there is a UserInfo table, such:

 

Now we need to remove the completely repeated data: select distinct * FROM dbo. UserInfo. The result is as follows:

　　

But now there is a new requirement. We need to deduplicate the name 'zhangsan', that is, if there is only one piece of data with the same name, it will be very troublesome if we still use DISTINCT. Presumably, you have come to think that ROW_NUMBER () is the time to make the debut. We only need to execute such a statement, and everything is fine. SELECT * FROM (SELECT ROW_NUMBER () OVER (partition by Name order by id) AS RowId, * FROM dbo. userInfo) x WHERE x. rowId = 1; although it looks a little long, it is easy to understand. The execution result is as follows:

Currently, only one data record with the same name is obtained, and the first one is obtained based on the order by id in the SQL statement. The deduplicated field is followed BY PARTITION, maybe you want to ask why you want to add WHERE x. rowId = 1. Now we do not add this condition to see how the result is: SELECT * FROM (SELECT ROW_NUMBER () OVER (partition by Name order by id) AS RowId, * FROM dbo. userInfo) x, for example:

　　

Now we can see that if no condition is added, all data will be identified, so the data with RowId = 1 is the de-duplicated data;

In addition to deduplication, ROW_NUMBER () can get the row number of the table. Now we only need the following statement: SELECT ROW_NUMBER () OVER (order by id) AS RowNum, * FROM dbo. userInfo. The result is as follows:

　　

All the data is obtained, and the row number of each data is obtained. The data ORDER can be adjusted according to order by id. That is to say, partition by Name is not required if the data is not duplicated, this function is also used in multiple pages. If 15 pieces of data are displayed on each page, add the condition RowNum Between 1 AND 15 to the page. Lone Wolf has very few blogs and is also relatively lazy at ordinary times, hoping to be useful to everyone.

SQL query statement, deduplication and removal of null Fields

Select distinct chnname, pasprt, phone, address
From gstprofile
Where pasprt is not null and len (pasprt) = 18 and phone is not null and address is not null

SQL statement deduplication

If it is only twice in a row, I can insert it into another table, and then the ID starts from 2 and then connects to the query.
DECLARE @ TB1 TABLE (id int identity (1, 1), num int) DECLARE @ TB2 TABLE (id int identity (2, 1), num int) insert into @ TB1 VALUES (111) insert into @ TB1 VALUES (111) insert into @ TB1 VALUES (222) insert into @ TB1 VALUES (222) insert into @ TB1 VALUES (333) insert into @ TB1 VALUES (111) insert into @ TB1 VALUES (111) insert into @ TB2 select num from @ TB1SELECT. ID,. num from @ TB1 a left join @ TB2 B ON. ID = B. id and. NUM = B. num where B. ID IS NULL