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

在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)