182.Duplicate Emails

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.

Solution
Approach I: Using GROUP BY and a temporary table [Accepted]
Approach II: Using GROUP BY and HAVING condition [Accepted]
Solution

Approach I: Using GROUP BY and a temporary table [Accepted]

Algorithm

Duplicated emails existed more than one time. To count the times each email exists, we can use the following code.

select Email, count(Email) as num
from Person
group by Email;

Email num [e-mail] 2 [e-mail] 1

Taking this as a temporary table, we can get a solution as below.

select Email from(  select Email, count(Email) as num  from Person  group by Email) as statisticwhere num > 1;

Approach II: Using GROUP BY and HAVING condition [Accepted]

A more common used way to add a condition to a GROUP BY is to use the HAVING clause, which is much simpler and more efficient.

select Emailfrom Persongroup by Emailhaving count(Email) > 1;

Answer3:

SELECT DISTINCT p1.EmailFROM Person p1, Persion p2WHERE p1.Email = p2.Email AND p1.ID <> p2.ID;

DISTINCT用法
CONCAT，GROUP_CONCAT