问题

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

示例：

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

根据以上输入，你的查询应返回以下结果：

+————-+ | Email | +————-+ | a@b.com | +————-+

思路

  1. select email
  2. from Person
  3. group by email
  4. having count(email) > 1;
  1. select Email, count(Email) as num
  2. from Person
  3. group by Email;
Email num
a@b.com 2
c@d.com 1

以此作为临时表，我们可以得到下面的解决方案

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