MySQL查詢表內重複記錄

標籤：rowid   bsp   row   from   sql   查詢   group by   seq   重複記錄   

1、尋找表中多餘的重複記錄，重複記錄是根據單個欄位（peopleId）來判斷select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、刪除表中多餘的重複記錄，重複記錄是根據單個欄位（peopleId）來判斷，只留有一個記錄delete from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)and min(id) not in (select id from people group by peopleId having count(peopleId )>1) 3、尋找表中多餘的重複記錄（多個欄位）select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 4、刪除表中多餘的重複記錄（多個欄位），只留有rowid最小的記錄delete from vitae awhere (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 awhere (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查詢表內重複記錄