MySQL重复数据处理_mysql 处理重复数据-CSDN博客

本文链接：https://blog.csdn.net/qq_34819372/article/details/120727101

本文介绍了如何使用SQL查询和删除数据库中重复的数据。首先，通过`GROUP BY`和`HAVING COUNT(1)>1`找出重复的记录，然后可以选择性地删除全部重复数据或者仅保留最新/最旧的一条。此外，还提供了保留重复数据中最新/最旧记录并删除其余的方法，这对于数据清洗和管理至关重要。

摘要由CSDN通过智能技术生成

1、查询重复数据

SELECT id,COUNT(1) FROM tablename GROUP BY material_id HAVING COUNT(1) >1 
//只获取ID
SELECT id FROM tablename GROUP BY material_id HAVING COUNT(1) >1

2、删除全部重复数据

//查询
SELECT * FROM tablename
WHERE 
id in 		(SELECT * FROM (SELECT id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a) 

//删除
DELETE FROM tablename
WHERE 
id in 		(SELECT * FROM (SELECT id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)

3、删除全部重复数据中最老/新的一条

获取最小/大ID 或最小/大时间的那些数据
MIN(id) - > MAX(id)
道理其实很好理解，满足重复项条件，同时是满足最小/大id或最小/大时间

//根据自增ID
SELECT * FROM tablename
WHERE 
material_id in 		(SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a) 
and 
id in (SELECT * FROM (SELECT MIN(id) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)


//根据时间
SELECT * FROM tablename
WHERE 
material_id in 		(SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a) 
and 
id in (SELECT * FROM (SELECT MIN(time) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)


//删除数据
DELETE FROM tablename 
WHERE 
material_id in 		(SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a) 
and 
id in (SELECT * FROM (SELECT MIN(id) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)

4、保留全部重复数据中最老/新的一条，删除其它

使用id not in 即可，排除

//根据自增ID
SELECT * FROM tablename
WHERE 
material_id in 		(SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a) 
and 
id not in (SELECT * FROM (SELECT MIN(id) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)


//根据时间
SELECT * FROM tablename
WHERE 
material_id in 		(SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a) 
and 
id not in (SELECT * FROM (SELECT MIN(time) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)


//删除数据
DELETE FROM tablename 
WHERE 
material_id in 		(SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a) 
and 
id not in (SELECT * FROM (SELECT MIN(id) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)