LeetCode 182. Duplicate Emails

題目

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.

題目大意:編寫一個SQL查詢從Person表中找出所有重複的郵箱地址。

解題思路

• GROUP BY HAVING

 SELECT `Email` FROM `Person` GROUP BY `Email` HAVING COUNT(*) > 1 ```

* 做笛卡爾積

  * DISTINCT
```SQL
 SELECT DISTINCT t1.`Email`  FROM `Person` AS t1, `Person` AS  t2   WHERE t1.id != t2.id and t1.`Email` = t2.`Email` ```

  * INNER JOIN

SQL
SELECT DISTINCT p.Email FROM Person p INNER JOIN Person q ON p.Id != q.Id AND p.Email = q.Email;

*測試下來使用group by 會更快一點*

## GROUP BY HAVING 官方文件 

[GROUP BY](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html) 資料分組(聚合)
[HAVING](https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html) 資料聚合後的篩選