Leetcode 196. Delete Duplicate Emails

196. Delete Duplicate Emails

原题目链接:196. Delete Duplicate Emails

写一条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.

举个例子,在运行完你的查询脚本之后,上述Person表格应该有下面所示的行信息:

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

注意:

你的输出是执行完脚本以后的整个Person表格。采用delete语句。

Solution

方法:采用DELETEWHERE语句[Accepted]

Algorithm

Email为条件将Person与自己相链接,我们可以得到以下代码:

1
2
3
4
5
6
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email
;

然后我们需要找出与其它记录拥有相同邮箱地址的更大的id,因此我们可以在WHERE语句当中增加一个条件如下。

1
2
3
4
5
6
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
;

事实上,我们已经找到需要删除的记录了,我们可以将这些语句放在一条DELETE语句后面。

MySQL

1
2
3
4
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id