leetcode182 查找重复的电子邮箱 Duplicate Emails

编写一个 SQL查询，来查找名为 Person 的表中的所有重复电子邮件。

 

 

 创建表和数据：

Create table If Not Exists Person (Id int,Email varchar(255));
Truncate table Person;
insert into Person (Id, Email) values ('1','a@b.com');
insert into Person (Id, Email) values ('2','c@d.com');
insert into Person (Id, Email) values ('3','a@b.com');

解法：

1.如果一个字段的值在表中重复了，那么含有重复值的行数一定超过1。

group by 对Email分组，那么Email重复的行个数大于1。

having 筛选出这些行。

select Email
from Person
group by Email
having count(Id) > 1

2.假设表中的字段Id是唯一的，还可以自连接表，选中哪些Id不同，Email相同的行。注意投影后要distinct去重。

select distinct P1.Email
from Person as P1 join Person as P2 on (P1.Id != P2.Id and P1.Email = P2.Email)

3.对每行的Email，子查询中都在表中查找一次，Email相同的行个数超过1。选中此行。效率低。

select distinct P1.Email
from Person as P1 
where 1 < (
    select count(*)
    from Person as P2
    where P1.email = P2.email
);

 

 

 

select E1.Name as Employee
from Employee as E1 join Employee as E2 on (E1.ManagerId  = E2.Id and E1.salary > E2.salary)