统计具有重复电子邮件ID的不同行
如何获得SQL中具有重复电子邮件ID的不同行的数量？
ID NAME EMAIL
1 John asd@asd.com
2 Sam asd@asd.com
4 Bob bob@asd.com
5 Tom asd@asd.com
6 Rob bob@asd.com
7 Tic tic@asd.com
8 Dad dad@asd.com
查询应该返回2. asd@asd.com和bob@asd.com是重复的
SELECT
COUNT(*)
FROM Users
GROUP BY EMail
HAVING ( COUNT(EMAIL) > 1 )
这个查询返回一些奇怪的结果。 谢谢
行数：
select sum(cnt)
from (select count(*) as cnt
from Users
group by email
having count(*) > 1) T
电子邮件数量：
select count(*)
from (select count(*) as cnt
from Users
group by email
having count(*) > 1) T
两者同时：
select count(*) as COUNT_EMAIL sum(cnt) as COUNT_ROWS
from (select count(*) as cnt
from Users
group by email
having count(*) > 1) T
以上应该适用于SQL Server和Oracle。
链接地址: http://www.djcxy.com/p/30509.html