題目:

Table: Person

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.

After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.

給定一個table,刪除裡面重複的email直到剩一個

又來了SQL,不過這次不是要SELECT,是來DELETE

DELETE FROM Person 
WHERE id NOT IN
(SELECT * FROM (SELECT Min(id) FROM Person GROUP BY Email) as result);

用GROUP BY用email分組,選出各組裡最小的id
刪除id不在這集合內的所有資料
這樣我們就將所有重複email都刪除了(留下id最小的)
最後執行時間449ms(faster than 98.51%)

那我們下題見