SQL查詢/删除重複記錄

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)