MySQL刪除重復記錄是我們經常用到的操作,下面為您介紹了5種MySQL刪除重復記錄的方法,如果您感興趣的話,不妨一看。
1、查找表中多余的重復記錄,重復記錄是根據單個字段peopleId)來判斷
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、刪除表中多余的重復記錄,重復記錄是根據單個字段peopleId)來判斷,只留有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)
3、查找表中多余的重復記錄多個字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、刪除表中多余的重復記錄多個字段),只留有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、查找表中多余的重復記錄多個字段),不包含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)
使用MySQL臨時表加速查詢
教您如何合理使用MySQL索引
5種常見的MySQL日志類型
自動恢復MySQL日志文件的方法
如何使用新的MySQL更新日志