1、某个字段重复,根据该字段去除重复值返回唯一值。
select distinct('字段名') from '表名'
2、查找表中全部的重复记录,根据单个字段判断重复记录
select * from '表名' t
where '字段名' in (select '字段名' from '表名' group by '字段名' having count('字段名')>1)
3、查找表中重复记录(只保留 ID 最大的一条记录)
select * from '表名' t
where id in (select max(id) from '表名' group by '字段名')
4、删除表中多余的重复记录,重复记录根据单个字段判断,只保留 ID 最大的记录
delete from '表名' t
where '字段名' in (select '字段名' from '表名' group by '字段名' having count('字段名') > 1)
and id not in (select max(id) from '表名' group by '字段名' having count('字段名')>1)
5、查找表中多余的重复记录,根据多个字段判断重复记录
select * from '表名' t
where ('字段1','字段2') in (select '字段1','字段2' from '表名' group by '字段1','字段2' having count(*) > 1)
6、删除表中多余的重复记录,重复记录根据多个字段判断,只保留 ID 最大的记录
delete from '表名' t
where ('字段1','字段2') in (select '字段1','字段2' from '表名' group by '字段1','字段2' having count(*) > 1)
and id not in (select max(id) from '表名' group by '字段1','字段2' having count(*)>1)