oracle刪除同一張表的重複記錄

標籤：

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)
關鍵看什麼欄位相同算重複，如果是arrearmain_id、reladdr、addrsourcetype的話，那這樣寫是最高效的，因為用了rowid: delete from cncc_customeraddr_tab t where t.rowid > (select min(x.rowid)   from cncc_customeraddr_tab x   where x.arrearmain_id = t.arrearmain_id   and x.reladdr = t.reladdr   and x.addrsourcetype = t.addrsourcetype)   and t.addrsourcetype = ‘1300000001‘

 

轉自：http://www.cnblogs.com/wjlstation/archive/2012/06/20/2555832.html

oracle刪除同一張表的重複記錄