MySQL查询表内重复记录

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

 

 

select * from people  where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

 

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有一个记录

 

delete from people  where peopleId in (select peopleId from people group by peopleId havingcount(peopleId) > 1)

and min(id) not in (select id from people group by peopleId having count(peopleId )>1)

 

3、查找表中多余的重复仿站记录（多个字段）

 

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

 

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

 

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

and rowid not in (select min(rowid) from vitae group by peopleId,seq havingcount(*)>1)

 

上一篇：如何使用DEDEcms/织梦制作复合筛选或联动筛选图解

下一篇：如何判断手机浏览器打开PC网站的时候跳转到移动