几个删除重复记录的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