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 地址

代码

1
2
3
4
5
6
7
8
9
-- 方法一

select 
	distinct p.Email 
from 
	Person p, 
	(select count(Email) as n, Email from Person group by Email) tmp 
where 
	p.Email = tmp.Email and tmp.n >= 2
1
2
3
-- 方法二

select Email from Person group by Email having count(*) >= 2
1
2
3
4
5
6
7
8
-- 方法三

select 
	distinct a.Email 
from 
	Person a inner join Person b on a.Email = b.Email 
where 
	a.Id <> b.Id
1
2
3
4
5
6
7
8
9
10
-- 方法四

select 
	distinct a.Email 
from 
	Person a 
where 
	exists(
		select 1 from Person b where a.Email = b.Email limit 1,1
	)
1
2
3
4
5
6
7
8
9
10
11
12
-- 方法五

select 
	distinct a.Email 
from 
	Person a 
	left join 
		(select * from Person group by Email) b 
	on 
		(a.Email = b.Email and a.Id = b.Id) 
where 
	b.Email is null