T-SQL filter Delete duplicate records and keep one

The following is a general t-SQL template for this operation:

/* Delete from a from -- delete Table A (a is the referenced table to be deleted)
(
Select row_number () over -- row_number () over is an auto-incrementing column generated from the beginning.
(
Partition by repeat column 1, repeat column 2, repeat column 3 --, repeat column N partition by is similar to group by but not an aggregate function is used to display all records, including repeated
Order by repeat column 1, repeat column 2, repeat column 3 --, repeat column N order by with row_number () over
) Rownumber -- rownumber: returns an alias of the result column returned by row_number in brackets.
From table name) alias of a -- a result table
Where a. rownumber> 1 -- the value of rownumber in Table A is greater than 1 (record more than one unique identifier )*/

 

Next is an example of this operation.Code:

Create Database Test

Go

Use test

Go

Create Table # Test (ID uniqueidentifier, name nvarchar (20), PWD nvarchar (20 ))

Insert into # Test (ID, name, PWD) values (newid (), 'hangsan', 'hangsan ')

Insert into # Test (ID, name, PWD) values (newid (), 'hangsan', 'hangsan1 ')

Insert into # Test (ID, name, PWD) values (newid (), 'hangsan', 'hangsan ')

Insert into # Test (ID, name, PWD) values (newid (), 'hangsan', 'hangsan ')

Insert into # Test (ID, name, PWD) values (newid (), 'lisi', 'lisi ')

Insert into # Test (ID, name, PWD) values (newid (), 'lisi', 'lisi ')

Insert into # Test (ID, name, PWD) values (newid (), 'lisi', 'lisi1 ')

Insert into # Test (ID, name, PWD) values (newid (), 'lisi', 'lisi ')

Insert into # Test (ID, name, PWD) values (newid (), 'lisi', 'lisi ')

Select * from # Test

-- Query out duplicate records and keep one result for comparison and test.

Select * from

(Select row_number () over

(

Partition by name, PWD

Order by name, PWD

) Rownumber, * from # Test

) A where a. rownumber> 1

-- Perform the delete operation (delete duplicate records and keep one record)

Delete from a from

(Select row_number () over

(

Partition by name, PWD

Order by name, PWD

) Rownumber from # Tes

) A where a. rownumber> 1