每日日报2021 12/8

今天我对数据的重复检测查询进行了一波检测。

查询表重复数据:
SELECT * FROM 表1 f WHERE (f.字段1,f.字段2) in (SELECT 字段1,字段2 FROM 表1 GROUP BY 字段1,字段2 HAVING count(*)>1);

查询表中多余的重复记录，只留有id最小的记录
select * FROM 表1 f WHERE (f.字段1,f.字段2) IN ( SELECT 字段1,字段2 FROM 表1 GROUP BY 字段1,字段2 HAVING count(*) > 1 ) AND id NOT IN ( SELECT min(id) FROM 表1 GROUP BY 字段1,字段2 HAVING count(*) > 1 )

删除表中多余的重复记录，只留有id最小的记录
DELETE FROM 表1 f WHERE (f.字段1,f.字段2) IN ( SELECT 字段1,字段2 FROM 表1 GROUP BY 字段1,字段2 HAVING count(*) > 1 ) AND id NOT IN ( SELECT min(id) FROM 表1 GROUP BY 字段1,字段2 HAVING count(*) > 1 )