SQL query deletes duplicate data

Database UserInfo

Delete duplicate user name delete duplicate username phone number the same user name phone number only one user is reserved

01. Querying duplicate data based on multiple fields

With Data1 as (

Select Mobilephone,name from UserInfo
GROUP BY Mobilephone,name
Having Count (*) >1

),

02. Duplicate Data Allocation number

Data2 as (

Select U.*,row_number () over (partition by U.mobilephone,u.name ORDER by Id) RowNum from UserInfo u
INNER JOIN Data1 D on U.name=d.name and U.mobilephone=d.mobilephone

),

03, the query needs to delete the data primary key (each group number is one of the retention of the other delete)

Data3 as (

Select Id from Data2 where rownum>1

)

04. Delete data based on primary key

Delete from UserInfo
where Id in (select ID from data3)

After deletion

The use of partition function partition by in SQL Server http://www.cnblogs.com/zhangchengye/p/5473860.html

SQL query deletes duplicate data