How to query duplicate records in MYSQL

There are many methods for querying duplicate records in MYSQL. The following describes several of the most common methods for querying duplicate records in MYSQL. It is helpful for you to learn how to query duplicate records in MYSQL.

1. Search for redundant duplicate records in the Table. duplicate records are determined based on the peopleId of a single field.

 
 

  
  
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. duplicate records are determined based on the peopleId of a single field. Only the records with the smallest rowid are retained.

 
 

  
  
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)   
 
 

3. Search for redundant fields in the table)

 
 

  
  
select * from vitae a   
  
  
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)   
 
 

4. Delete multiple fields with the least rowid.

 
 

  
  
delete from vitae a   
  
  
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 multiple fields of redundant record records in the table), excluding records with the smallest rowid

 
 

  
  
select * from vitae a   
  
  
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)   
 
 

Implementation of MySQL random Query

How to query intermediate records in MySQL

MySQL query results are sorted by a certain value

Use functions to query row numbers in MySQL

Non-empty question in MySQL Query