182.查找重复的电子邮箱(查找表内的重复元素)

问题描述

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

示例：

Person：
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

返回结果：
+---------+
| Email   |
+---------+
| a@b.com |
+---------+
说明：所有的电子邮箱都是小写字母的

我的解决

**思路： **先查找出重复的记录；对重复的记录去重

select distinct(Email) from Person
where Email in (select Email from Person group by Email having count(Email) > 1 )

题解的思路

1、使用group by和临时表

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

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

2、使用group by和having

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

SQL重复记录查询

sql重复记录查询