删除单表中多个字段联合为重复数据的数据，只保留id最大的数据

最新推荐文章于 2024-05-06 10:59:21 发布

danjiawei

最新推荐文章于 2024-05-06 10:59:21 发布

阅读量189

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/danjiawei/article/details/107558627

版权

--方法一：

DELETE 
FROM
	`user` 
WHERE
( NAME, age ) IN ( SELECT name,age FROM (SELECT NAME, age FROM `user` GROUP BY NAME, age HAVING count( * ) > 1) t1 ) 
AND id NOT IN ( SELECT id FROM (SELECT MAX( id ) AS id FROM USER GROUP BY NAME, age HAVING count( * ) > 1) t2 )

--方法二：
DELETE 
FROM
	`user` 
WHERE
id IN (
SELECT id FROM
(SELECT id 
 FROM
	`user` t1 
 WHERE id NOT IN ( SELECT MAX( id ) FROM `user` WHERE NAME = t1.NAME AND age = t1.age ) 
 ) t2 )