Query and delete SQL statements of Repeated Records and query SQL statements of records

Query and delete SQL statements of Repeated Records and query SQL statements of records

SQL statement for querying and deleting 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 (select peopleId from people group by peopleId having count (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 peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

Note: rowid is not required for oracle .....

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, seq having 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 by 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 by peopleId, seq having count (*)> 1)

And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

Obtain 【]Java source code SpringMVC Mybatis Shiro Bootstrap Rest Webservice