196.删除重复的电子邮箱
leetcode链接
1. 题目
编写一个 SQL 查询,来删除 Person
表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
2. 解题
方法1:分组找出最小id
delete from Person where id not in (
select t.id from (select min(id) id from Person group by Email) t
);
步骤详解:
step1:找出经过Email分组后的最小的id,也就是id=1和id=2
select min(id) id from Person group by Email
step2:将step1的查询结果作为临时表t
select t.id from (select min(id) id from Person group by Email) t
step3:删除不在表t中的id,也就是id=3
方法2:自连接
delete from Person where id in (
select t.Id from (select p2.Id as Id from Person p1, Person p2 where p1.Id<p2.Id and p1.Email=p2.Email) t
);
和方法1一样的框架,不一样的是利用自连接找出Email相同但id不一样的Email
方法3:官方解答
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
另外,建立临时表的思路可以查看这篇博客的内容:https://blog.csdn.net/qq_41946557/article/details/99437062