动态层级父子结构下，递归将子节点Nominal值汇总至父节点

需求说明 #

需要将账户表中Nominal列的值递归汇总到对应的父节点，最终每个节点的Nominal值为自身Nominal值 + 所有子节点（含多级后代）的Nominal总和。

数据结构与初始化脚本 #

CREATE TABLE [dbo].[Account](
    [AccountSeq] [bigint]  NOT NULL PRIMARY KEY,
    [ParentAccountSeq] [bigint] NULL,
    [AccountCode] [nvarchar](50) NOT NULL,
    [AccountName] [nvarchar](50) NOT NULL,
    [Nominal] [decimal](18, 2) NOT NULL
) 

INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) 
VALUES 
    (1, NULL, N'101', N'AKTIVA LANCAR', CAST(0.00 AS Decimal(18, 2)))
    ,(2, 1, N'10101', N'KAS', CAST(0.00 AS Decimal(18, 2)))
    ,(3, 2, N'1010101', N'KAS KECIL (IDR)', CAST(10000.00 AS Decimal(18, 2)))
    ,(4, 2, N'1010102', N'KAS KECIL ($$$)', CAST(15000.00 AS Decimal(18, 2)))
    ,(5, 1, N'10102', N'BANK', CAST(0.00 AS Decimal(18, 2)))
    ,(6, 5, N'1010201', N'BCA PKU AC: 220.391', CAST(20000.00 AS Decimal(18, 2)))
    ,(7, 5, N'1010202', N'BCA PKU AC: 220.279', CAST(25000.00 AS Decimal(18, 2)))
    ,(8, 1, N'10104', N'PIUTANG USAHA', CAST(30000.00 AS Decimal(18, 2)))
    ,(10, 1, N'10105', N'PIUTANG PROYEK', CAST(40000.00 AS Decimal(18, 2)))
    ,(11, NULL, N'201', N'HUTANG JANGKA PENDEK', CAST(50000.00 AS Decimal(18, 2)))
    ,(12, NULL, N'301', N'MODAL', CAST(60000.00 AS Decimal(18, 2)))

原代码问题分析 #

你提供的CTE查询是从根节点向下遍历，错误地将父节点的Nominal值累加到子节点上，这与需求的向上汇总子节点数值到父节点逻辑完全相反，因此无法得到正确结果。

解决方案 #

方法1：递归从叶子节点向上汇总 #

这种方法先定位所有叶子节点（无后代的节点），然后递归向上将子节点的累计值汇总到父节点，最后处理多子节点的合并：

WITH cteAccountHierarchy AS (
    -- 锚点：叶子节点（没有子节点的账户）
    SELECT 
        AccountSeq,
        ParentAccountSeq,
        AccountCode,
        AccountName,
        Nominal
    FROM [Account] a
    WHERE NOT EXISTS (SELECT 1 FROM [Account] WHERE ParentAccountSeq = a.AccountSeq)

    UNION ALL

    -- 递归：向上将子节点的累计值加到父节点
    SELECT 
        p.AccountSeq,
        p.ParentAccountSeq,
        p.AccountCode,
        p.AccountName,
        CAST(p.Nominal + c.Nominal AS DECIMAL(18,2))
    FROM [Account] p
    INNER JOIN cteAccountHierarchy c ON p.AccountSeq = c.ParentAccountSeq
),
cteFinal AS (
    -- 合并同一父节点的多个子节点累计值
    SELECT 
        AccountSeq,
        ParentAccountSeq,
        AccountCode,
        AccountName,
        SUM(Nominal) AS Nominal
    FROM cteAccountHierarchy
    GROUP BY AccountSeq, ParentAccountSeq, AccountCode, AccountName
    -- 补充无后代的根节点（如11、12号账户）
    UNION ALL
    SELECT 
        AccountSeq,
        ParentAccountSeq,
        AccountCode,
        AccountName,
        Nominal
    FROM [Account]
    WHERE ParentAccountSeq IS NULL 
      AND NOT EXISTS (SELECT 1 FROM [Account] WHERE ParentAccountSeq = AccountSeq)
)
SELECT * 
FROM cteFinal
ORDER BY AccountSeq;

方法2：生成所有后代节点后求和 #

这种方法先递归找出每个节点的所有后代（包括自身），再通过分组求和得到每个节点的累计值，逻辑更直观：

WITH cteDescendants AS (
    -- 锚点：每个节点自身
    SELECT 
        AccountSeq AS AncestorSeq,
        AccountSeq,
        Nominal
    FROM [Account]

    UNION ALL

    -- 递归：获取当前节点的所有后代节点
    SELECT 
        c.AncestorSeq,
        a.AccountSeq,
        a.Nominal
    FROM cteDescendants c
    INNER JOIN [Account] a ON c.AccountSeq = a.ParentAccountSeq
)
SELECT 
    a.AccountSeq,
    a.ParentAccountSeq,
    a.AccountCode,
    a.AccountName,
    SUM(d.Nominal) AS Nominal
FROM [Account] a
INNER JOIN cteDescendants d ON a.AccountSeq = d.AncestorSeq
GROUP BY a.AccountSeq, a.ParentAccountSeq, a.AccountCode, a.AccountName
ORDER BY a.AccountSeq;

两种方法都能得到预期结果，方法2更易理解，方法1在数据量较大时性能可能更优。

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