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

?