方法一 Person自身做Cartesian product where子句

哇这个有点妙哦

思路：

找出所有要被删掉的。重复的且id更大的

delete clause 删除

select p1.* from Person as p1, Person as p2
where p1.email=p2.email and p1.id>p2.id;

delete p1 from Person as p1, Person as p2
where p1.email=p2.email and p1.id>p2.id;

方法二使用子查询group by计算数量

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
delete from Person
/* 不支持在同一个表中删除查询 可以中间建表
where email in (
    select email from Person
    group by email having count(*)>1
);*/
where email in (
    select temp.email from (
        select email from Person
        group by email having count(*)>1
    ) as temp
)
and id not in (
    select temp2.mid from (
        select min(id) as mid from Person
        group by email having count(*)>1
    ) as temp2
);

改进

这里group by之后都不用用having筛选count的数量，因为只有一条数据的就是最小的id了。
其实这里count(*)都可以省略。

delete from Person where id not in
(
    select mid from 
    (
        select min(id) as mid /*count(*) as cnt */ from Person
        group by email
    ) as t
);