(转)删除表中重复数据sql语句

查询表中重复数据 
select * from employee where employeeId in (select employeeId from employee group by employeeId having count(employeeId) > 1)

删除表中多余的重复记录，重复记录是根据单个字段（employeeId）来判断，只留有rowid最小的记录 
delete from employee where employeeId in (select employeeId from employee group by employeeId having count(employeeId) > 1) 
and rowid not in (select min(rowid) from employee group by employeeId having count(employeeId )>1)

查找表中多余的重复记录（多个字段） 
select * from employee e 
where (e.employeeId,e.phoneNo) in (select employeeId,phoneNo from employee group by employeeId,phoneNo having count(*) > 1)

删除表中多余的重复记录（多个字段），只留有rowid最小的记录 
delete from employee e 
where (e.employeeId, e.phoneNo) in (select employeeId, phoneNo from employee group by employeeId,phoneNo having count(*) > 1) 
and rowid not in (select min(rowid) from employee group by employeeId,phoneNo having count(*)>1)

查找表中多余的重复记录（多个字段），不包含rowid最小的记录 
select * from employee e 
where (e.employeeId,e.phoneNo) in (select employeeId, phoneNo from employee group by employeeId,phoneNo having count(*) > 1) 
and rowid not in (select min(rowid) from employee group by employeeId,phoneNo having count(*)>1)

通用:

delete from table t 
where (t.字段1, t.字段2, … , t.字段n) in (select 字段1, 字段2, … , 字段n from table group by 字段1, 字段2, … , 字段n having count(*) > 1)
and rowid not in (select min(rowid) from table group by 字段1, 字段2, … , 字段n having count(*)>1)

此外:

如果只是查询的时候, 不显示重复, 只需select distinct 字段 from table….
--------------------- 
作者：关耳木 
来源：CSDN 
原文：https://blog.csdn.net/germoo/article/details/79960480 
版权声明：本文为博主原创文章，转载请附上博文链接！