编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明:所有电子邮箱都是小写字母。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/duplicate-emails
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
思路1:先分组,然后找数据大于1的组。
select Email from Person group by Email having count(distinct Id) > 1;
思路2:自连接,然后找Email相等且id不等的组。
select distinct a.Email from Person as a inner join Person as b on a.Email=b.Email and not a.Id=b.Id;