SQL如何实现多列分组时分别统计各层级的聚合计数值

错误原因 #

你原有SQL按major和minor两个字段共同分组，count()统计的是每个(major, minor)组合的行数，因此无法拿到单个major维度下的总记录数。

实现方案 #

方案1：窗口函数（适配支持窗口函数的主流SQL版本，如MySQL 8.0+、PostgreSQL、Oracle等） #

通过分区统计直接得到两个维度的计数，写法简洁：

SELECT DISTINCT
    major,
    minor,
    COUNT(*) OVER(PARTITION BY major) AS major_count,
    COUNT(*) OVER(PARTITION BY major, minor) AS minor_count
FROM `table`

方案2：子查询关联（适配不支持窗口函数的旧版SQL） #

先单独统计每个major的总条数，再和(major, minor)维度的统计结果关联：

SELECT
    a.major,
    a.minor,
    b.major_count,
    COUNT(*) AS minor_count
FROM `table` a
INNER JOIN (
    SELECT major, COUNT(*) AS major_count
    FROM `table`
    GROUP BY major
) b ON a.major = b.major
GROUP BY a.major, a.minor, b.major_count

两种方案运行后均可得到你需要的统计结果。

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