SQL database deduplication

Source: Internet
Author: User
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 U

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 U

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.