编写SQL查询以删除名为Person的表中所有重复的电子邮件条目,并仅基于其最小ID保留唯一的电子邮件。
![](https://i-blog.csdnimg.cn/blog_migrate/ab8ff5ae69cac00219100aa672a48a29.png)
Clue: 先找出email不重复的id,并从小到大排序定义为表a;然后从表中删除不存在a中的数据。
首先定义表a,用group by
select min(id) id from person
group by email
Output:
![](https://i-blog.csdnimg.cn/blog_migrate/850b9b6f2312eda3bcdc67f2b75a4b1b.png)
然后从person表删除不在a中的数据
delete from person p
where id not in(
select * from (
select min(id) id from person
group by email) a)
Output:
![](https://i-blog.csdnimg.cn/blog_migrate/ebb08d921ee79061fdce683ea4e7a0ef.png)
PS: 一定要写select * from 或者select a.id from不然会出现1093错误Error Code: 1093. You can't specify target table 'p' for update in FROM clause