Query and delete duplicate records

(a)
1, find redundant records in the table, duplicate records are based on a single field (Peopleid) to determine the
Select * from people
where Peopleid in (select Peopleid from PEO PLE GROUP BY Peopleid have count (Peopleid) > 1)
2, delete redundant records in a table, and duplicate records are judged by a single field (Peopleid), leaving only the ROWID minimum records
Delete from people
where Peopleid in (select Peopleid to People group by Peopleid have count (Peopleid) > 1)
and rowID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >1)
3, find redundant records in the lookup table (multiple fields)
Select * from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ has CO UNT (*) > 1)
4, delete redundant records in the table (multiple fields), leaving only rowid minimal records
Delete from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopl Eid,seq from Vitae GROUP by Peopleid,seq having count (*) > 1)
and rowID not in (select Min. rowid) from Vitae Group by PEOPLEID,SEQ has count (*) >1)

5, find redundant records in the table (multiple fields), not including ROWID minimum records
SELECT * from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from V Itae GROUP BY PEOPLEID,SEQ has count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by Peopleid,se Q has count (*) >1)
(ii)
For example,
There is a field "name" in Table A,
and the "name" value may be the same between different records, and
now needs to query between the records in that table, " A duplicate entry exists for the name value, and
Select Name,count (*) from A Group by Name has Count (*) > 1
If the gender is also the same, the following:
Select Name,*,co UNT (*) from A Group by name,* have Count (*) > 1

(c)
Method One
Declare @max integer, @id integer
Declare cur_rows cursor Local for select main field, COUNT (*) from table name Grou The P by main field has count (*) >; 1
Open cur_rows
Fetch cur_rows into @id, and @max
while @ @fetch_status =0
begin Select @max = @max-1
Set rowcount @max
Delete from table name where main field = @id
Fetch cur_rows into @id, @max
End
Close Cur_rows
Set ROWCOUNT 0
method Two
has duplicate records in two sense, one that is completely duplicated, that is, records that are duplicates for all fields, two records that are duplicated in some key fields, such as the Name field repeats, and other fields that do not Must be repeated or repeated to be ignored.
1, for the first repetition, easier to resolve, using
SELECT DISTINCT * FROM TableName
to get a result set without duplicate records.
If the table needs to delete duplicate records (1 records are retained for duplicates), you can delete
SELECT DISTINCT * into #Tmp from tablename
drop table tablename
Selec The reason for this duplication occurs with the T * into TableName from #Tmp
drop table #Tmp
is that the table is poorly designed and can be resolved by adding a unique index column.
2, this type of repetition typically requires that the first record in the duplicate record be preserved, as follows
Assuming that there are duplicate fields name,address, the result set of the two field is required
Select Identity (int,1,1) as Autoid, * into #Tmp tablename
Select min (autoid) as autoid to #Tmp2 from #Tmp GROUP by Name,autoid
Select * from #Tmp where autoid to (select Autoid from #tmp2)
The last select is the result of name,address non-repetition Set (But one more autoid field that can be written in the SELECT clause to omit this column)
(d)
Query repeats
SELECT * FROM tablename where ID in (
Select ID from table Name
GROUP BY ID
has count (id) > 1
)
3, find redundant records in the table (multiple fields)
Select * from Vitae a
where (a. PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
running can cause problems where (a.peop LEID,A.SEQ) Such a writing is not a pass!!!