delete from Person
where id not in (
select min(Id) id from Person group by Email having count(Email) > 1
) or Email not in (
select Email from Person group by Email having count(Email) = 1
)
执行这条语句时会报错:You can't specify target table 'Person' for update in FROM clause
这是因为MySQL不允许同时查询和删除一张表,我们可以通过子查询的方式包装一下即可避免这个报错
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
)
这样就不会报错了