Sqlserver Delete duplicate records

Sqlserver Delete duplicate records

1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleid ).
Select
* From people
Where peopleid in (selectPeopleidFrom
PeopleGroupByPeopleidHavingCount (peopleid)
> 1)

2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid
Delete
From people
Where peopleidIn (selectPeopleidFrom
PeopleGroupByPeopleidHaving
Count (peopleid)> 1)
And rowid not in (select Min (rowid) from
PeopleGroup by peopleidHaving count (peopleid
)> 1)

3. Search for redundant duplicate records in the table (multiple fields)
Select * From vitae
Where (A. peopleid, A. seq)
In(Select peopleid, seq from vitae group by peopleid, seqHaving
Count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
Delete from vitae
Where
(A. peopleid, A. seq) in(Select peopleid, seq from vitae group
Peopleid, seq having count (*)> 1)
And rowid not in (select Min (rowid) from
Vitae group by peopleid, seq having count (*)> 1)

5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
Select * From vitae
Where
(A. peopleid, A. seq) in(Select peopleid, seq from vitae group
Peopleid, seq having count (*)> 1)
And rowid not in (select Min (rowid) from
Vitae group by peopleid, seq having count (*)> 1)

(2)
For example
There is a field "name" in Table ",
The "name" value may be the same for different records,
Now, you need to query items with duplicate "name" values between records in the table;
Select
Name, count (*) from a group by name having count (*)> 1

If the gender is also the same, the statement is as follows:
Select name, sex, count (*) from a group by name, sex having
Count (*)> 1