[MySQL][LeetCode][Easy] 196. Delete Duplicate Emails

心得

題目要求刪除同樣Email的資料,在DELETE的條件WHERE裡面不能包含異動的表(Person),所以這裡再包了一層SELECT來排除編譯錯誤的問題,而這層必須給予別名(強制)否則會編譯錯誤。

問題

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
9
10
11
12
13
14
15
16
17
18

+----+------------------+
>
> | Id | Email |
>
> +----+------------------+
>
> | 1 | [email protected] |
>
> | 2 | [email protected] |
>
> | 3 | [email protected] |
>
> +----+------------------+
>
> 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
7
8
9
10
11
12
13
14

+----+------------------+
>
> | Id | Email |
>
> +----+------------------+
>
> | 1 | [email protected] |
>
> | 2 | [email protected] |
>
> +----+------------------+
>
>

答案

1
2
3
4
5
6
7
8

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