写SQL语句查询Person表中所有重复的电子邮箱
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
例如,你的语句对上表应该返回如下结果
+---------+ | Email | +---------+ | a@b.com | +---------+
注意:所有的emails都是小写
1:先按邮箱Email分组,生成临时表A,表A中有Email和对应出现的次数。然后判断次数是否大于1
SELECT Email
FROM (SELECT Email, COUNT(*) AS Sum
FROM Person
GROUP BY Email) AS A
WHERE A.Sum>1
2:利用HAVING
WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(*)>1
算法题来自:https://leetcode-cn.com/problems/duplicate-emails/description/