SQL repeat record troubleshooting methods

1. Search for duplicate records

① If you just don't want repeated records in the query results, you can add Distinct

Select distinct * from TestTable

② 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

③ The ID is unique, but 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, Place)

2. Delete duplicate records
① One idea is to use a temporary table to fill the queried non-Repeated Records in the temporary table, and then fill the records in 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 records with unique IDs but repeated fields (that is, query the largest ID in the same field record by field, then keep this record, and delete other records ). (The group by field is a little troublesome ).

Delete TestTable where UserID not in
(Select max (UserID) as UserID from TestTable group by UserName, Sex, Place)