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

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

SQL如下

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

注意

主语句删除操作

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

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

SELECT * FROM ([结果集]) A