Leetcode 182. Duplicate Emails (Database)

Write a SQL query to find all duplicate emails in a table named Person.

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

For example, your query should return the following for the above table:

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

Note: All emails are in lowercase.

 where里面不能用count，因为where后面跟的条件必须是一行可以确定的，而count需要数一下table中一共有多少行，显然不满足这个要求。多以需要用group by + having。

# Write your MySQL query statement below
select Email from Person GROUP BY Email HAVING count(Email) > 1

 

常见思路，由于是在table中按某个条件选择一部分row，而且这个条件牵扯到其他row。常用的方法是使用table的多个copy。

# Write your MySQL query statement below
select distinct p1.Email from Person p1, Person p2 where p1.Email = p2.Email and p1.Id != p2.Id