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;