LeetCode查找重复的电子邮箱SQL

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

示例:

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


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

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


说明:所有电子邮箱都是小写字母。

思路一

# Write your MySQL query statement below
select Email from Person group by Email having count(Email) > 1

备注:

优先顺序。where>group by>having>order by

where 在 group by 之前执行,having 在 group by 之后执行。如果上面把 having 改为 where ,就会产生 group by 还没有分组完,就执行了计数。

思路二

# Write your MySQL query statement below
select Email from (select Email , count(Email) as num from Person group by Email) as statistic  where num > 1

备注:

以 Email 分组,以 Email 个数作为 num 列。然后以此当做辅助表 statistic ,从辅助表里取 num > 1 的,筛选出重复的

原文地址:https://www.cnblogs.com/huangzs/p/14171325.html