写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
复制代码