ORACLE删除重复记录方法
几个删除重复记录的SQL语句
1.用rowid方法
2.用group by方法
?
现有一个人员表persons，有三个成员：ID，CARDID，PNAME
1.用rowid方法
select * from persons a where rowid != (select max(rowid) from persons b where a.id = b.id and a.cardid = b.cardid and a.pname = b.pname)
?
?删除：
delete from persons a where rowid != (select max(rowid) from persons b where a.id = b.id and a.cardid = b.cardid and a.pname = b.pname)--无法处理为NULL情况
?
2.用group by方法
查找：
select t.* from base.persons t group by t.id, t.cardid, t.pnamehaving count(*) > 1;
?
delete from base.persons x where (x.id, x.cardid, x.pname) in (select t.id, t.cardid, t.pname from base.persons t group by t.id, t.cardid, t.pname having count(*) > 1);--但是这种对id,cardid两列相同，pname为空的处理不了，因为in无法处理NULL
?
