1、单个字段查询、去重
查询单字段重复数据
select * from 表名称
where 字段 in (select 字段 from 表名称 group by 字段 having count(1) > 1)
单个字段去重,保留一条
注意count(字段)会忽略空值,如果空值也要去重使用count(1) 或者 count(*)。
效率count(主键) > count(1) > count(字段) > coung(*)
min可以换max,oracle中也可以用rowid
delete from 表名称
where 字段 in (select 字段 from 表名称 group by 字段 having count(1) > 1)
and id not in (select min(id) from 表名称 group by 字段 having count(1) > 1)
2、多字段查询、去重
查询多字段重复数据查询
select * from 表名称
where (字段1,字段2) in (select 字段1,字段2 from 表名称 group by 字段1,字段2 having count(1) > 1)
多个字段去重,保留一条
注意:此方法不能去除 字段1,字段2 同时为null的情况,同时为null需要另外写sql
delete from 表名称
where (字段1,字段2) in (select 字段1,字段2 from 表名称 group by 字段1,字段2 having count(1) > 1)
and id not in (select min(id) from 表名称 group by 字段1,字段2 having count(1) > 1)
多字段同时为null,保留一条
delete from 表名称
where 字段1 is null and 字段2 is null
and id not in (select min(id) from test where 字段1 is null and 字段2 is null )