编写一个 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
