题目(简单)
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
根据以上输入,你的查询应返回以下结果:
±--------+
| Email |
±--------+
| a@b.com |
±--------+
说明:所有电子邮箱都是小写字母。
解题语句
使用子查询
SELECT
t.Email
FROM
(SELECT
p.Email,
SUM(
CASE
WHEN p.Email = pe.Email
THEN 1
END
) AS num
FROM
Person AS p,
(SELECT DISTINCT
Email
FROM
Person) AS pe
GROUP BY p.Email) AS t
WHERE t.num > 1
这里的思路可以分为三步:
- 先找出所有的邮箱(去重)
- 计算出每个邮箱出现的次数(计数)
- 筛选出出现次数大于一的(筛选)
使用 group by 与 having
SELECT
Email
FROM
Person
GROUP BY Email
HAVING COUNT(Email) > 1
这种方式比较推荐
使用自连接
select distinct
a.email as Email
from
person a ,
person b
where
a.id<>b.id
and a.email=b.email;
这种方式在数据量多的情况下性能应该不好。