递归SQL查询实现金额累加问题求助

原查询的问题分析 #

1. 递归方向错误：原代码从顶层节点向下遍历，但结果取的是子节点的信息，且将子节点金额与父节点金额相加，逻辑完全颠倒，无法实现父节点汇总子节点金额的需求。
2. 字段计算错误：s.ParentCoaid + r.parentcoaid属于无意义的字段拼接，应该保留节点原本的parentcoaid值。
3. 结果范围错误：仅筛选level = 1的记录，只能得到顶层节点，无法展示所有层级的节点及对应累加金额。

修正后的查询语句 #

WITH RecursiveCTE AS (
    -- 自顶向下遍历，确定每个节点的层级，并记录根节点（自身）用于后续汇总
    SELECT 
        coaid,
        name,
        parentcoaid,
        amount,
        coaid AS root_id,
        1 AS level
    FROM accounts
    WHERE parentcoaid = 0
    UNION ALL
    SELECT 
        a.coaid,
        a.name,
        a.parentcoaid,
        a.amount,
        rc.root_id,
        rc.level + 1 AS level
    FROM accounts a
    INNER JOIN RecursiveCTE rc ON a.parentcoaid = rc.coaid
)
-- 按根节点分组，汇总当前节点及所有子孙节点的金额
SELECT 
    base.coaid,
    base.name,
    base.parentcoaid,
    SUM(all_nodes.amount) AS amount,
    base.level
FROM RecursiveCTE base
JOIN RecursiveCTE all_nodes ON base.coaid = all_nodes.root_id
GROUP BY base.coaid, base.name, base.parentcoaid, base.level
ORDER BY base.level, base.coaid;

逻辑说明 #

1. 自顶向下遍历：从顶层节点（parentcoaid = 0）出发，遍历所有子节点，为每个节点标记层级，并记录每个节点对应的根节点（即自身），用于后续汇总该节点及所有子孙的金额。
2. 汇总金额：通过自连接关联每个节点与其所有子孙节点，分组求和得到该节点自身金额加上所有子孙节点金额的总和。
3. 结果排序：按层级和节点ID排序，与期望结果的展示顺序一致。

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