D8-leetcode182. 查找重复的电子邮箱(简单)

编写一个 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）表自身进行左连接。

select * from Person a left join Person b on a.Id=b.Id;

(2)查询Id不同，但是Email相同的Email名称

select a.Email from Person a left join Person b on a.Id=b.Id
where a.Id!=b.Id and a.Email=b.Email;

换思路：
重复-->出现次数大于1

考虑到group by 和去重
group by +having

select Email,count(1) from Person 
group by Email;

image.png

select Email,count(1) as con  from Person 
group by Email
where con>1;

用where 报错换成having

select Email,count(1) as con  from Person 
group by Email
having con>1;

修改代码使得输出格式满足题目要求:

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

注意：group by 中的筛选只能用having。

如果不用having,用where进行筛选,需要嵌套进行查询

select Email,count(1) as con  from Person 
group by Email;

嵌套后用where进行筛选

select Email from 
(select Email,count(1) as con  from Person 
group by Email) a 
where con>1;

总结：

• 把题目量化便于思考。
• having+group by