leetcode上的数据库表去重问题

这是我在leetcode上遇到的一个题目：
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  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
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  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

一开始我的想法实在来简单，想直接用子查询来进行删除：

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

好久没写SQL，发现我这个想法实在太幼稚了。
先来一发题目的讨论

you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

不能写表的时候进行内查询引用。最后来一发比较好的答案：

delete p1 from Person p1,Person p2 where 
    p1.Email = p2.Email 
        and
    p1.Id > p2.Id