MySQL删除重复数据中id最大的数据

参考博客：https://www.cnblogs.com/jiangxiaobo/p/6589541.html

SQL如下

DELETE
FROM
	person
WHERE
	personnum IN (
		SELECT
			n.PERSONNUM
		FROM
			(
				SELECT
					c.personnum
				FROM
					person c
				WHERE
					c.CPYCOD = '0006'
				GROUP BY
					c.personnum
				HAVING
					count(c.personnum) > 1
			) n
	)
AND id IN (
	SELECT
		m.id
	FROM
		(
			SELECT
				max(g.id) AS id
			FROM
				person g
			GROUP BY
				g.PERSONNUM
			HAVING
				count(g.PERSONNUM) > 1
		) m
);

注意

主语句删除操作

DELETE FORM PERSON 
WHERE 
PERSONNUM IN([重复的PERSONNUM数据])
AND ID IN([重复数据中id最大值])

重复的PERSONNUM数据和重复的id数据作为结果集需要用临时表再查询一次，如下：

SELECT * FROM ([结果集]) A