使用oracle刪除表中重複記錄

標籤：HERE   記錄   strong   card   person   重複   nbsp   方法   str   

 (1)使用用rowid方法

       查詢重複資料:select * from person a where rowid !=(select max(rowid) from person b where a.cardid=b.cardid and a.pname=b.pname);

       重複資料刪除資料:delete from person a where rowid !=(select max(rowid) from person b where a.cardid=b.cardid and a.pname=b.pname);

(2)使用group by方法

     查詢重複資料:select * from person where cardid in (select cardid from person group by cardid having count(cardid)>1);

     重複資料刪除資料:delete from person where cardid in (select cardid from person group by cardid having count(cardid)>1) and rowid not in (select min(rowid) from person                                group by cardid having count(cardid)>1);

使用oracle刪除表中重複記錄