leetcode-sql-182-查找重复的电子邮箱

查找重复的电子邮箱
/**
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
根据以上输入，你的查询应返回以下结果：

±--------+
| Email |
±--------+
| a@b.com |
±--------+
说明：所有电子邮箱都是小写字母。
*/

 insert into tb_Person_182 (email) values ("a@b.com"),("c@d.com"),("a@b.com");

– 方法一：先查出临时表email以及出现次数
– step1:

select email,count(id) as num from tb_Person_182 group by email;

– step2:

select email from (select email,count(id) as num from tb_Person_182 group by email) a where num >1;

– 方法二：自联 如果存在email相同ID不同的那么该email重复

select distinct a.email as email from tb_Person_182 a  join tb_Person_182 b on a.email = b.email and a.id != b.id;

– 方法三：与方法一相似，换种写法

with tmp as (
    select email,count(id) as num from tb_Person_182 group by email
)
select email from tmp where num >1;

– 方法四：group by having email个数大于1的即为重复

select email from tb_Person_182 group by email having count(id) >1;