MySQL不使用GROUP BY如何实现分月状态透视聚合统计

已知前提 #

• 待处理表为transfer，包含3个字段：
  • idx：记录唯一标识
  • statusIdx：状态值，取值1/2/3分别对应status1、status2、status3三类状态
  • date：业务发生日期
• 需求要求：
  • 按年月维度横向透视，单行输出对应年月下三类状态的计数
  • 结果末尾追加全量合计行
  • 实现过程禁止使用GROUP BY子句
• 此前方案问题：直接使用row_number()、count() over()实现时，结果按statusIdx拆分为多行垂直统计，未实现单行聚合透视效果。

实现逻辑 #

不用GROUP BY实现聚合透视的核心是条件窗口聚合+行号去重：

1. 用CASE表达式做状态判断，配合带年月分区的SUM() OVER()窗口函数，直接在每行计算出对应年月下各状态的总计数，同一年月的所有行计算结果完全一致
2. 用ROW_NUMBER() OVER()给同一年月的记录打上行号，只取每个年月分区行号为1的记录，实现去重，避免同一年月返回多行
3. 用UNION ALL拼接全量合计行，合计行的窗口函数不设置分区，直接计算全表各状态计数，同样取行号为1的单条记录即可。

可直接运行的SQL代码 #

SELECT 年月, status1_cnt, status2_cnt, status3_cnt
FROM (
    SELECT
        DATE_FORMAT(`date`, '%Y-%m') AS 年月,
        SUM(CASE WHEN statusIdx = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY DATE_FORMAT(`date`, '%Y-%m')) AS status1_cnt,
        SUM(CASE WHEN statusIdx = 2 THEN 1 ELSE 0 END) OVER (PARTITION BY DATE_FORMAT(`date`, '%Y-%m')) AS status2_cnt,
        SUM(CASE WHEN statusIdx = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY DATE_FORMAT(`date`, '%Y-%m')) AS status3_cnt,
        ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(`date`, '%Y-%m') ORDER BY idx) AS rn
    FROM transfer
) t
WHERE rn = 1

UNION ALL

SELECT '合计' AS 年月, status1_cnt, status2_cnt, status3_cnt
FROM (
    SELECT
        SUM(CASE WHEN statusIdx = 1 THEN 1 ELSE 0 END) OVER () AS status1_cnt,
        SUM(CASE WHEN statusIdx = 2 THEN 1 ELSE 0 END) OVER () AS status2_cnt,
        SUM(CASE WHEN statusIdx = 3 THEN 1 ELSE 0 END) OVER () AS status3_cnt,
        ROW_NUMBER() OVER (ORDER BY idx) AS rn
    FROM transfer
) t_total
WHERE rn = 1;

注意事项 #

• 该方案适用于MySQL 8.0及以上支持窗口函数的版本
• 全程未使用GROUP BY子句，完全符合要求
• 透视结果为单行对应一个年月的统计值，不会出现按状态拆分为多行的问题
• 合计行固定在结果集末尾，字段和上方年月统计行完全对齐

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