SQL查询疑难：如何获取每个b类型对应计数最大的a值及对应计数

你遇到的核心问题是：当你把a加入SELECT列表却只按b分组时，数据库无法确定要返回哪个a值——因为每个b对应多个a的分组结果，这种情况下数据库的行为是未定义的（不同数据库处理逻辑不同，但大多会随机选取该分组内的任意一个a），所以才会出现a_with_max_b值不对的情况。

要实现「每个b对应计数最大的a和该最大值」，有两种常用且可靠的方法：

方法1：使用窗口函数（推荐，现代SQL标准） #

窗口函数可以轻松实现分组内的排序和筛选，逻辑清晰且效率高：

WITH grouped_data AS (
    -- 先统计每个(a,b)组合的出现次数
    SELECT a, b, COUNT(*) AS count_of_b
    FROM my_table
    GROUP BY a, b
),
ranked_data AS (
    -- 对每个b分组，按count_of_b降序排名
    SELECT 
        b,
        a AS a_with_max_b,
        count_of_b,
        ROW_NUMBER() OVER (PARTITION BY b ORDER BY count_of_b DESC) AS rn
    FROM grouped_data
)
-- 取每个b分组中排名第一的行（即count最大的a）
SELECT b, a_with_max_b, count_of_b AS `MAX(count_of_b)`
FROM ranked_data
WHERE rn = 1;

• 如果存在多个a对同一个b的计数相同且都是最大值，ROW_NUMBER()会只返回其中一个；如果想返回所有并列最大值的行，把ROW_NUMBER()换成RANK()即可。

方法2：使用关联子查询（兼容旧版数据库） #

如果你的数据库不支持窗口函数，可以用关联子查询来匹配每个b对应的最大计数值：

SELECT 
    g.b,
    g.a AS a_with_max_b,
    g.count_of_b AS `MAX(count_of_b)`
FROM (
    -- 先统计每个(a,b)组合的出现次数
    SELECT a, b, COUNT(*) AS count_of_b
    FROM my_table
    GROUP BY a, b
) g
-- 筛选出当前b分组中count等于该b最大count的行
WHERE g.count_of_b = (
    SELECT MAX(count_of_b)
    FROM (
        SELECT a, b, COUNT(*) AS count_of_b
        FROM my_table
        GROUP BY a, b
    ) g2
    WHERE g2.b = g.b
);

这种方法同样能得到正确结果，且如果有并列最大值，会返回所有符合条件的行。

为什么你的原语句不对？ #

当你执行：

SELECT b, a a_with_max_b , MAX(count_of_b) 
FROM (SELECT a, b, COUNT(b) count_of_b FROM my_table GROUP BY a, b) 
GROUP BY b;

这违反了SQL标准的「分组规则」：SELECT列表中的列要么是GROUP BY中的分组列，要么是被聚合函数包裹的列。有些数据库（比如MySQL在关闭ONLY_FULL_GROUP_BY模式时）允许这种写法，但会随机返回该b分组内的任意一个a值，完全不会关联到MAX(count_of_b)对应的那个a——这就是你得到错误a值的原因。

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