SQL中如何编写包含多级汇总的复杂财务报表？-SQL-PHP中文网

rollup 是生成多级财务报表小计与总计的最直接解法，它按字段顺序生成 (a,b,c)、(a,b,null)、(a,null,null)、(null,null,null) 四层组合，需配合 grouping() 区分真实 null 与汇总占位符。

sql中如何编写包含多级汇总的复杂财务报表？

GROUP BY 多列 + ROLLUP 是最直接的解法

SQL 标准里没有“财务报表”专用语法，但 ROLLUP 能原生支持多级小计（如：部门 → 科目 → 月份 → 行汇总 → 部门汇总 → 总计）。它比手动 UNION ALL 多个 GROUP BY 更可靠，也比应用层拼接更可控。

常见错误是把 ROLLUP(a, b, c) 理解成“先按 a 汇总、再按 b 汇总”，实际生成的是 (a,b,c)、(a,b,NULL)、(a,NULL,NULL)、(NULL,NULL,NULL) 四层组合 —— NULL 值就是小计/总计行的标记。

ROLLUP 必须配合 GROUP BY 使用，不能单独存在
字段顺序决定汇总层级：越靠前的字段，汇总粒度越粗（例如 ROLLUP(dept, account, month) 中 dept 的小计包含所有 account 和 month）
MySQL 8.0+、PostgreSQL 9.5+、SQL Server、Oracle 都支持；SQLite 不支持，得换 GROUPING SETS 或分步查

用 GROUPING() 区分真实 NULL 和汇总占位符

财务报表里，科目余额本身可能是 0 或 NULL，而 ROLLUP 生成的汇总行也会让非关键字段变成 NULL。不加区分会导致“某部门某科目为 NULL”被误读成数据缺失，实际只是小计行。

GROUPING() 函数返回 1（该字段参与了汇总）或 0（该字段有真实值），必须和 ROLLUP 同时使用才有效。

SELECT 
  CASE WHEN GROUPING(dept) = 1 THEN '总计' ELSE dept END AS dept,
  CASE WHEN GROUPING(account) = 1 THEN '小计' ELSE account END AS account,
  SUM(amount) AS total
FROM ledger
GROUP BY dept, account WITH ROLLUP;

不要在 WHERE 中过滤 GROUPING()=0 —— 这会把汇总行全干掉
多个字段时，GROUPING(dept, account) 是语法错误，只能单字段调用
PostgreSQL 用 GROUPING(dept)，MySQL 同样；SQL Server 还支持 GROUPING_ID() 返回位掩码，适合判断复合汇总状态

避免 SUM(CASE WHEN ...) 嵌套过深导致可读性崩溃

财务报表常需“本期发生额”“累计发生额”“年初余额”“期末余额”等多列计算，有人习惯一层套一层 CASE WHEN，结果 SQL 超过 200 行、改一个条件要通读全篇。

更稳的做法是：用 CTE 先做基础聚合，再用窗口函数或自连接算累计值。尤其当“累计”依赖时间顺序（如按月滚动加总）时，SUM() OVER (ORDER BY month) 比关联子查询快且逻辑清晰。

别在 GROUP BY 后直接写 SUM(CASE WHEN period='202401' THEN amount END) + 11 个类似字段 —— 可维护性极差
如果数据库不支持窗口函数（如 MySQL 5.7），优先用临时表存月度汇总，再用 JOIN 拼累计，比嵌套 SELECT 容易调试
注意 NULL 在 SUM() 中自动忽略，但 COALESCE(SUM(...), 0) 要加在最外层，否则中间 CASE 返回 NULL 会导致整列变 NULL

日期维度处理不当会让“年月小计”出错

财务要求“按自然月汇总”，但原始数据里的 datetime 字段如果直接 GROUP BY YEAR(dt), MONTH(dt)，会跨年混在一起（比如 2023-12 和 2024-01 都算作 “12 月”）。更糟的是，用 DATE_FORMAT(dt, '%Y%m') 在某些数据库里无法走索引。

安全做法是统一转成“月初日期”作为分组键，既语义清晰又利于索引利用：

GROUP BY DATE_SUB(dt, INTERVAL DAYOFMONTH(dt)-1 DAY)

MySQL 用 DATE_SUB(dt, INTERVAL DAYOFMONTH(dt)-1 DAY) 得到当月1日
PostgreSQL 用 DATE_TRUNC('month', dt)
SQL Server 用 DATEFROMPARTS(YEAR(dt), MONTH(dt), 1)
别用 CONCAT(YEAR(dt), '-', MONTH(dt)) —— 字符串分组慢，且 1 月变成 “2024-1” 而不是 “2024-01”，排序错乱

多级汇总真正难的不是语法，而是对“哪一层该出现哪些字段、哪些值该为空、哪些空是合法汇总占位符”的持续校验。上线前务必拿手工账核对至少三笔跨层级数据，特别是部门小计是否漏了某个科目的某个月份。