问题
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
1
2
3
4
5
6
7
8
| +----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id is the primary key column for this table.
|
For example, after running your query, the above Person table should have the following rows:
1
2
3
4
5
6
| +----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
|
翻译
从Person表中删除所有重复的邮箱，仅保留 id 最小的邮箱。
解决方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| DELETE
FROM
Person
WHERE
id NOT IN (
SELECT
t.id
FROM
(
SELECT
`Email`,
min(id) AS id
FROM
Person
GROUP BY
`Email`
) t
)
|