如何查找表中的重复记录并用一条sql删除

假设test表有三个字段,id,pid,name,无主键

 查找重复记录

select  id,pid,name from test group by id,pid,name having count(*) > 1;

 

 删除重复记录

delete from test where rowid not in (select max(rowid) from test group by id,pid,name);

 

查找所有重复记录

select * from test a
where exists(select 1 from test b where a.id = b.id and a.pid = b.pid and a.name = b.name and a.rowid<>b.rowid)