?
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.
在LeetCode做的第一道SQL题目，刚刚学SQL，好好记录下为好。
自己的代码：
?
select Email as Email from Person group by Email having count(Id) > 1;
时间用得有点多，足足1044ms;
?
摘录下网上其他人的代码：
1、
?
select distinct p1.Email
as Email
from Person p1, Person p2
where p1.Email = p2.Email
and p1.Id != p2.Id
1215ms;
?
2、
select distinct p1.Email
as Email
from Person p1 join Person p2
on p1.Email = p2.Email
where p1.Id != p2.Id
1058ms;
?
3、
?
select distinct p1.Email
as Email
from Person p1 join Person p2
on p1.Email = p2.Email and p1.Id != p2.Id
1359ms;
?
4、
?
select distinct Email
from Person p1
where exists (
select Email
from Person p2
where p1.Email = p2.Email
limit 1, 1
) 1204ms;
?
5、
?
select distinct Email
from Person p1
where exists (
select 1
from Person p2
where p1.Email = p2.Email
limit 1, 1
) 1178ms;
?
6、
?
select distinct Email
from Person p1
where exists (
select Email
from Person p2
where p1.Email = p2.Email and p1.Id <> p2.Id
) 1849ms;
?
7、
?
select distinct Email
from Person p1
where exists (
select Email
from Person p2
where p1.Email = p2.Email and p1.Id <> p2.Id
limit 1
) 1291ms;
?
注意到limit 1表示是限定在前1行，limit 1, 1表示从第2行开始取1个记录，limit n等价于limit 0, n
8、
?
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
1080ms;
?
注意如果最后一行是
?
where b.Email = NULL
则WA，因为这是赋值。
?
也可以这样：
?
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 isnull(b.Email)
?