leetcode with MySQL:196. Delete Duplicate Emails

题目:

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%)

那我们下题见