LeetCode： Duplicate Emails

LeetCode： 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.

---

将自身连接，等值项为email，然后寻找id不同email相同的元组。
第一次写这种题，数据库还真难。看来还有很长的路要走。

---

# Write your MySQL query statement below
select distinct C.AEmail as Email
from
(select A.Id as AId,A.Email as AEmail,B.Id as BId,B.Email as BEmail
from Person as A,Person as B
where A.Email=B.Email) as C
where C.AId<>C.BId    
and C.AEmail=C.BEmail;