# 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 相同。