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.pname
having 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