WITH RECURSIVE 是展开多级会计科目树的唯一通用方案，需区分锚成员（如 parent_id IS NULL）和递归成员（JOIN 自身别名），禁止聚合/窗口函数，并加终止条件（如 level 限制）。
用 WITH RECURSIVE 展开多级会计科目树
财务系统里科目通常是树状结构（如：1001→1001.01→1001.01.001），直接 JOIN 多次不现实，也难动态适配任意深度。PostgreSQL、SQL Server、MySQL 8.0+、SQLite 3.8.3+ 都支持 WITH RECURSIVE，这是唯一能干净展开无限层级的方案。
关键点在于区分锚成员（根节点）和递归成员（子节点）：
- 锚查询必须能明确选出所有一级科目（比如
parent_id IS NULL或level = 1） - 递归查询的
JOIN条件必须指向自身别名（如JOIN account_tree t ON t.parent_id = r.id），且不能出现聚合或窗口函数 - 务必加终止条件，例如限制最大递归深度：
WHERE r.level ，避免死循环
WITH RECURSIVE account_tree AS ( SELECT id, code, name, parent_id, 1 AS level FROM account WHERE parent_id IS NULL -- 一级科目 UNION ALL SELECT a.id, a.code, a.name, a.parent_id, t.level + 1 FROM account a JOIN account_tree t ON a.parent_id = t.id WHERE t.level < 6 -- 防止意外无限递归 ) SELECT * FROM account_tree ORDER BY code;
在递归结果上关联余额表做逐级汇总
只展开科目树没用，真正要的是“每个科目及其所有下级科目的期末余额总和”。这里不能先 SUM() 再递归，而必须先递归出完整路径关系，再 JOIN 余额表，最后按当前科目分组求和。
常见错误是把余额表提前 JOIN 到递归 CTE 里——这会导致同一笔余额被多次计入不同层级，虚增金额。
- 余额表（
balance）应只在主查询中与递归结果JOIN - 连接条件是
balance.account_id = account_tree.id，不是balance.account_id = account_tree.leaf_id（不存在这种字段） - 若需区分期初/期末/发生额，确保
balance表有period字段并正确过滤（如WHERE balance.period = '2024-12'）
WITH RECURSIVE account_tree AS ( SELECT id, code, name, parent_id FROM account WHERE parent_id IS NULL UNION ALL SELECT a.id, a.code, a.name, a.parent_id FROM account a JOIN account_tree t ON a.parent_id = t.id ) SELECT t.code, t.name, COALESCE(SUM(b.amount), 0) AS total_balance FROM account_tree t LEFT JOIN balance b ON b.account_id = t.id AND b.period = '2024-12' GROUP BY t.id, t.code, t.name ORDER BY t.code;
处理“末级科目无余额但上级需汇总”的场景
有些科目（如“管理费用”）本身不记账，只作为汇总节点存在，其 id 在 balance 表里查不到任何记录。上面的 LEFT JOIN 能让这类科目显示 0，但真实需求往往是：把它的所有子孙余额加起来——哪怕它自己没数据。
这就需要反向关联：不是“科目→余额”，而是“余额→所有上级科目”。此时递归 CTE 的方向要调换，从余额记录出发向上追溯至根节点。
- 锚查询改为从
balance表取数，带出直接对应的account_id - 递归部分通过
JOIN account找该科目的parent_id，直到parent_id IS NULL - 最终按
ancestor_id（即各级上级）分组求和，才能得到每个科目的完整归属余额
WITH RECURSIVE balance_with_ancestors AS ( -- 锚：每笔余额及其直接科目 SELECT b.account_id AS leaf_id, b.account_id AS ancestor_id, b.amount, b.period FROM balance b WHERE b.period = '2024-12' UNION ALL -- 递归：找上级科目 SELECT b.leaf_id, a.parent_id, b.amount, b.period FROM balance_with_ancestors b JOIN account a ON b.ancestor_id = a.id AND a.parent_id IS NOT NULL ) SELECT a.code, a.name, COALESCE(SUM(b.amount), 0) AS rolled_up_balance FROM account a LEFT JOIN balance_with_ancestors b ON a.id = b.ancestor_id GROUP BY a.id, a.code, a.name ORDER BY a.code;
Oracle 用户注意：CONNECT BY 替代方案与坑
Oracle 不支持 WITH RECURSIVE，得用 START WITH ... CONNECT BY。语法差异大，且默认不保证层级顺序，容易导致汇总错位。
-
CONNECT BY PRIOR id = parent_id是标准写法，别写反成PRIOR parent_id = id（会变成自环） - 必须用
ORDER SIBLINGS BY控制同级顺序，否则LEVEL和路径可能混乱 -
NOCYCLE要显式加上，防止科目配置错误形成环（比如 A→B→A） - Oracle 的
CONNECT BY无法在递归分支里引用外部表，所以余额汇总仍需主查询外层JOIN，逻辑和上面第二段一致
复杂报表里最易被忽略的，是科目树变更后余额归属的时效性——如果某科目昨天刚从“应收账款”移到“其他应收款”，历史余额不会自动重算。这类业务规则必须在应用层或调度任务中显式处理，SQL 本身做不到跨期动态重分类。
