196. Delete Duplicate Emails - 删除重复的电子邮箱 ＜easy＞

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

提示：

执行 SQL 之后，输出是整个 Person 表。
使用 delete 语句。

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 is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:
Note:Your output is the whole Person table after executing your sql. Use delete statement.

思路：删除掉出现多次的邮箱记录，id not in (id ~ count(Email)>1) , 并且保证只有一个邮箱的不被删除

delete from Person 
where id not in (
    select id from (select min(Id) id from Person group by Email having count(Email) > 1) a
) and Email not in (
    select Email from (select Email from Person group by Email having count(Email) = 1) b
)

更简单的想法是只保留第一次出现的邮箱：

DELETE from Person 
Where Id not in (
    Select Id 
    From(
    Select MIN(Id) as id
    From Person 
    Group by Email
   ) t
)