首页 文章

具有重复电子邮件ID的不同行的计数

提问于
浏览
4

如何在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. as as@@@dd.com和bob@asd.com是重复的

SELECT 
 COUNT(*)  
FROM Users
GROUP BY EMail 
HAVING ( COUNT(EMAIL) > 1 )

此查询返回一些奇怪的结果 . 谢谢

1 回答

  • 7

    行数:

    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 .

相关问题