This article is a detailed analysis of the implementation of the Oracle Query and deletion job SQL, the need for a friend under the reference
Query and delete duplicate records of SQL statements
1, find redundant records in a table, duplicate records are based on a single field (Peopleid) to determine the
select * FROM People
where Peopleid in (select peopleid from People group by PeopleId has cou NT (Peopleid) > 1)
2, delete redundant records in a table, duplicate records are judged by a single field (Peopleid), leaving only the rowID minimal records
Delete from People
where Peopleid in (select Peopleid from people group by the having Co UNT (Peopleid) > 1)
and rowID not in (select min (rowid) from People GROUP by Peopleid H aving count (Peopleid) >1)
Note: rowid for Oracle self-band ...
3, find redundant records in the table (multiple fields)
select * from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopl Eid,seq from Vitae GROUP by Peopleid,seq having count (*) > 1)
4, delete redundant records in the table (multiple fields), leaving only the ROWID minimum records Delete from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae groupby PEOPLEID,SEQ has count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have CO UNT (*) >1)
5, find redundant records in the table (multiple fields), do not contain the ROWID minimum record
select * from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select Min (rowid) from Vitae GROUP by PEOPLEID,SEQ 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,
now is the time to query between the records in the table, the "name" value has duplicates, and the
Select name,count (*) from A Group by name has Count (*) > 1
If you also check the same gender also the same as the following:
Select name,sex,count (*) from A Group by Name,sex have Count (*) > 1
(iii)
method One
declare @max integer, @id integer
Declare cur_rows cursor Local for select main field, Count (*) from table name Group by main field having count (*) >; 1
Open cur_rows
Fetch cur_rows to @id, @max
while @ @fetch ......