182. Duplicate Emails

题目：

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

+----+---------+| Id | Email   |+----+---------+| 1  | [e-mail] || 2  | [e-mail] || 3  | [e-mail] |+----+---------+

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

+---------+| Email   |+---------+| [e-mail] |+---------+

Note: All emails are in lowercase.

艾玛 终于有一道我会的并且运行结果是finished的了~~~

select emailfrom Persongroup by emailhaving count(*)>1

Runtime: 97 ms

@1337c0d3r  这位 给了几个其他答案

  
1.Use self join.

SELECT DISTINCT a.Email FROM Person a JOIN Person b ON (a.Email = b.Email) WHERE a.Id <> b.Id

Runtime: 190 ms

2.Use subquery with EXISTS:

SELECT DISTINCT a.Email FROM Person a WHERE EXISTS(     SELECT 1     FROM Person b     WHERE a.Email = b.Email     LIMIT 1, 1 )

Runtime: 98 ms

3.Basic idea is this query:

SELECT DISTINCT Email FROM Person MINUS (SELECT Id, Email FROM Person GROUP BY Email)

But since MySQL does not support MINUS, we use LEFT JOIN:

SELECT DISTINCT a.Email FROM Person a LEFT JOIN (SELECT Id, Email from Person GROUP BY Email) b ON (a.email = b.email) AND (a.Id = b.Id) WHERE b.Email IS NULL

Runtime: 107 ms


不知道如果数据量变大 这几个方法哪个会更有效？