如何在MySQL中统计相同邮箱前缀对应不同域名的出现次数

你当前SQL的核心问题是分组粒度过细：将完整邮箱字段email加入了GROUP BY子句，每个唯一的完整邮箱天然只对应一个域名，因此COUNT(DISTINCT email_domain)的结果永远为1，无法统计到同一个clean_email关联多个域名的情况。

实现逻辑为：仅按clean_email分组，统计不同域名总数、聚合所有关联域名，最后用HAVING过滤出拥有多个不同域名的记录。不同数据库的字符串聚合语法有差异，对应实现如下：

MySQL / MariaDB 版本 #

SELECT 
    clean_email,
    COUNT(DISTINCT email_domain) AS domain_count,
    GROUP_CONCAT(DISTINCT email_domain ORDER BY email_domain SEPARATOR ', ') AS all_domains
    -- 如需同时查看关联的完整邮箱，可添加下行
    -- , GROUP_CONCAT(DISTINCT email ORDER BY email SEPARATOR ', ') AS all_emails
FROM email
GROUP BY clean_email
HAVING domain_count > 1;

PostgreSQL 版本 #

SELECT 
    clean_email,
    COUNT(DISTINCT email_domain) AS domain_count,
    STRING_AGG(DISTINCT email_domain, ', ' ORDER BY email_domain) AS all_domains
FROM email
GROUP BY clean_email
HAVING COUNT(DISTINCT email_domain) > 1;

SQL Server 2017+ 版本 #

SELECT 
    clean_email,
    COUNT(DISTINCT email_domain) AS domain_count,
    STRING_AGG(DISTINCT email_domain, ', ') WITHIN GROUP (ORDER BY email_domain) AS all_domains
FROM email
GROUP BY clean_email
HAVING COUNT(DISTINCT email_domain) > 1;

Oracle 11gR2+ 版本 #

SELECT 
    clean_email,
    COUNT(DISTINCT email_domain) AS domain_count,
    LISTAGG(DISTINCT email_domain, ', ') WITHIN GROUP (ORDER BY email_domain) AS all_domains
FROM email
GROUP BY clean_email
HAVING COUNT(DISTINCT email_domain) > 1;

内容的提问来源于stack exchange，提问作者JumpJ