查询或删除表中重复数据sql

Ratel 2019年06月02日 236次浏览

查询表中重复数据

select * from employee where employeeId in (select employeeId from employee group by employeeId having count(employeeId) > 1)

删除表中多余的重复记录,重复记录是根据单个字段(employeeId)来判断,只留有主键或其他可以判断大小的唯一字段最小的记录

delete from employee where employeeId in (select employeeId from employee group by employeeId having count(employeeId) > 1) and 主键或其他可以判断大小的唯一字段 not in (select min(主键或其他可以判断大小的唯一字段) 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)

删除表中多余的重复记录(多个字段),只留有主键或其他可以判断大小的唯一字段最小的记录

delete from employee e where (e.employeeId, e.phoneNo) in (select employeeId, phoneNo from employee group by employeeId,phoneNo having count(*) > 1) and 主键或其他可以判断大小的唯一字段 not in (select min(主键或其他可以判断大小的唯一字段) from employee group by employeeId,phoneNo having count(*)>1)

查找表中多余的重复记录(多个字段),不包含主键或其他可以判断大小的唯一字段最小的记录

select * from employee e where (e.employeeId,e.phoneNo) in (select employeeId, phoneNo from employee group by employeeId,phoneNo having count(*) > 1) and 主键或其他可以判断大小的唯一字段 not in (select min(主键或其他可以判断大小的唯一字段) 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 主键或其他可以判断大小的唯一字段 not in (select min(主键或其他可以判断大小的唯一字段) from table group by 字段1, 字段2, … , 字段n having count(*)>1)

此外:

如果只是查询的时候, 不显示重复
select distinct 字段 from table…