Mysql实现"删除重复的电子邮箱"的两种方法

写SQL语句删除Person表中所有重复的邮箱数据，重复的邮箱只保留Id最小的邮箱数据

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

例如，在运行查询语句之后，上表的保留的数据结果为

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

1：先分组找出所有邮箱对应的最小Id，然后用NOT IN删除多余重复的邮箱

DELETE FROM Person
WHERE Id NOT IN (SELECT * FROM (SELECT MIN(Id)
                 FROM Person
                 GROUP BY Email) AS A)

2：让两个表以邮箱关联起来，然后把相同邮箱且Id大的删除掉

DELETE P1 FROM Person P1, Person P2
WHERE P1.Email=P2.Email AND P1.Id>P2.Id

 上面是纯WHERE操作，下面结合JOIN，效果一致（参考：https://www.cnblogs.com/grandyang/p/5371227.html）

DELETE P1 FROM Person P1 JOIN Person P2
ON P1.Email=P2.Email WHERE P1.Id>P2.Id

算法题来自：https://leetcode-cn.com/problems/delete-duplicate-emails/description/