526互联

SQL删除重复的数据，只保留更新时间为最新的重复数据

发布时间 2023-06-15 18:35:28作者: 18sui

先查询出来是不是想要删除的数据
SELECT * FROM student 
WHERE name IN (
	SELECT name FROM student 
	WHERE name != ' ' 
	GROUP BY name 
	HAVING COUNT(name) > 1
)
AND modifytime NOT IN (
	SELECT MAX(modifytime) FROM student 
	GROUP BY name 
	HAVING COUNT(name) > 1
);



确定是需要删除的数据之后，执行删除语句（删除重要数据前记得先备份！！！）
DELETE FROM student 
WHERE name IN (
	SELECT name FROM student 
	WHERE name != ' ' 
	GROUP BY name 
	HAVING COUNT(name) > 1
)
AND modifytime NOT IN (
	SELECT MAX(modifytime) FROM student 
	GROUP BY name 
	HAVING COUNT(name) > 1
);