题目:
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明:所有电子邮箱都是小写字母。
SQL架构:
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');
解题语句:
方法一:单表查询:GROUP BY + HAVING
SELECT Email FROM Person GROUP BY Email HAVING COUNT( * ) > 1;
- 注:COUNT( * ) 比 COUNT( Email ) 效率高
方法二:子查询,创建临时表作为数据源
SELECT
Email
FROM
( SELECT Email, COUNT( Email ) AS c FROM Person GROUP BY Email ) AS p
WHERE
c > 1;
方法三:自关联
SELECT DISTINCT
p1.Email AS Email
FROM
Person AS p1
INNER JOIN Person AS p2 ON p1.Id != p2.Id
AND p1.Email = p2.Email;
效率上,单表查询 优于 关联查询 优于 子查询。