SQL如何实现MAX COUNT查询，让每个ID仅返回1条用户数最高的邮箱域名结果

实现方案（兼容所有支持窗口函数的主流数据库：MySQL 8.0+/PostgreSQL/SQL Server/Oracle） #

你需要的逻辑是先按Id、EmailDomain分组统计用户数，再筛选出每个Id下用户数唯一最高的记录，对应实现代码如下：

WITH GroupedData AS (
    -- 按Id和邮箱域名分组统计用户数
    SELECT
        Id,
        EmailDomain,
        COUNT(Users) AS UserCount
    FROM Table_Morgan
    GROUP BY Id, EmailDomain
),
IdMaxStat AS (
    -- 统计每个Id的最大用户数、以及达到最大用户数的记录条数
    SELECT
        Id,
        MAX(UserCount) AS MaxUserCount,
        COUNT(*) AS MaxRecordCnt
    FROM GroupedData
    GROUP BY Id
)
-- 关联筛选：只保留单Id下唯一的最高用户数记录
SELECT g.Id, g.EmailDomain, g.UserCount
FROM GroupedData g
INNER JOIN IdMaxStat m 
    ON g.Id = m.Id 
    AND g.UserCount = m.MaxUserCount
WHERE m.MaxRecordCnt = 1;

执行后输出结果和你给出的期望完全一致：Id1保留用户数最高的@gmail.com记录，Id2保留唯一的@hotmail.com记录，Id3因为两个域名用户数并列最高被过滤。

原有代码报错原因 #

你收到的错误提示：

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

以及子查询返回多条结果的报错，核心原因有两个：

1. 内层子查询缺失SELECT关键字，语法不合法
2. 逻辑偏差：你需要的是每个Id分组内的最大用户数，而原有代码的子查询是统计全表所有域名的最大用户数，逻辑不匹配，且如果全表有多个域名达到最大值时子查询会返回多条结果，用=匹配自然报错。

老版本数据库兼容方案（不支持CTE/窗口函数） #

如果使用的是不支持CTE的老版本MySQL等数据库，可以用嵌套子查询实现：

SELECT g.Id, g.EmailDomain, g.UserCount
FROM (
    SELECT Id, EmailDomain, COUNT(Users) AS UserCount
    FROM Table_Morgan
    GROUP BY Id, EmailDomain
) g
INNER JOIN (
    SELECT 
        Id, 
        MAX(UserCount) AS MaxUserCount,
        COUNT(*) AS MaxRecordCnt
    FROM (
        SELECT Id, EmailDomain, COUNT(Users) AS UserCount
        FROM Table_Morgan
        GROUP BY Id, EmailDomain
    ) t
    GROUP BY Id
) m ON g.Id = m.Id AND g.UserCount = m.MaxUserCount
WHERE m.MaxRecordCnt = 1;

可选：并列最高时任选一条的方案 #

如果你的需求是同一个Id有多个并列最高的域名时不需要过滤，任选一条即可，可以用更简洁的ROW_NUMBER窗口函数实现：

SELECT Id, EmailDomain, UserCount
FROM (
    SELECT
        Id,
        EmailDomain,
        COUNT(Users) AS UserCount,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY COUNT(Users) DESC) AS rn
    FROM Table_Morgan
    GROUP BY Id, EmailDomain
) t
WHERE rn = 1;

你可以在ORDER BY后补充额外排序规则（比如按邮箱域名字典序）来固定选择哪条并列记录。

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