# Description
邮件地址表：
+----+---------+
| Id | Email |
+----+---------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+---------+
1
2
3
4
5
6
7
2
3
4
5
6
7
删除重复的邮件地址：
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
1
2
3
4
5
6
2
3
4
5
6
# Solution
只保留相同 Email 中 Id 最小的那一个，然后删除其它的。
连接查询：
DELETE p1
FROM
Person p1,
Person p2
WHERE
p1.Email = p2.Email
AND p1.Id > p2.Id
1
2
3
4
5
6
7
2
3
4
5
6
7
子查询：
DELETE
FROM
Person
WHERE
id NOT IN (
SELECT id
FROM (
SELECT min( id ) AS id
FROM Person
GROUP BY email
) AS m
);
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
应该注意的是上述解法额外嵌套了一个 SELECT 语句，如果不这么做，会出现错误：You can't specify target table 'Person' for update in FROM clause。以下演示了这种错误解法。
DELETE
FROM
Person
WHERE
id NOT IN (
SELECT min( id ) AS id
FROM Person
GROUP BY email
);
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
参考：pMySQL Error 1093 - Can't specify target table for update in FROM clause (opens new window)
# SQL Schema
与 182 相同。