心得

題目要求刪除同樣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.

+----+------------------+
| 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:

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

答案

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