SQL record duplication

AboutSQLDuplicate records 1 Find duplicate records

① If you do not want repeated records in the query results,You can addDistinct

Select Distinct * FromTesttable

② If You Want to query repeated records and their quantity

Select Userid, username, Count ( * ) As ' Number of records '
From Testtable
Group By Userid, username
Having Count ( * ) > 1

③ IDNo duplicates,However, only one record with repeated fields is displayed.

Select * From Testtable Where Userid In
( Select Max (Userid) As Userid From Testtable Group By Username, sex, plac E)

2 Delete duplicate records

① One idea is to use a temporary table,Fill in the queried non-repeated records to the temporary table,Then fill in the records of the temporary table back to the original table.

Select Distinct * Into # Temp From Testtable
Drop Table Testtable
Select * Into Testtable From # Temp
Drop Table # Temp

② Delete ID No duplicates , Record with duplicate fields ( That is, the largest record of the same field is queried by field ID, Then keep this record , Delete other records ). (Group Field , A little troublesome ).

Delete Testtable Where Userid Not In
( Select Max (Userid) As Userid From Testtable Group By Username, sex, place)