SQL Server计算父子表子节点总和：父节点有值时排除孙节点值

现有Pools表，需为每个父节点计算其子节点的金额总和，核心规则：

若某子节点自身已设置Amount值，则该子节点的所有孙节点金额不纳入父节点的总和计算。

表结构 #

CREATE TABLE Pools (
    Id INT PRIMARY KEY,
    ParentId INT,
    Amount DECIMAL(18,2) NULL,
    FOREIGN KEY (ParentId) REFERENCES Pools(Id)
);

测试数据 #

INSERT INTO Pools (Id, ParentId, Amount) VALUES
(1, NULL, NULL),
(2, 1, NULL),
(3, 1, 100.00),
(4, 2, 50.00),
(5, 2, NULL),
(6, 5, 30.00);

预期输出 #

解决方案 #

使用递归CTE实现，核心思路是标记终止节点（自身有Amount的节点），并仅在父节点无Amount时向上关联，确保终止节点的后代不会被计入更高层父节点的总和：

WITH RecursivePools AS (
    -- 锚点：初始化所有节点，标记是否为终止节点
    SELECT 
        Id,
        ParentId,
        Amount,
        CASE WHEN Amount IS NOT NULL THEN 1 ELSE 0 END AS IsTerminating
    FROM Pools
    UNION ALL
    -- 递归：仅父节点无Amount时，继续向上关联父节点
    SELECT 
        rp.Id,
        p.ParentId,
        rp.Amount,
        rp.IsTerminating
    FROM RecursivePools rp
    JOIN Pools p ON rp.ParentId = p.Id
    WHERE p.Amount IS NULL
)
-- 汇总每个父节点的有效终止节点金额
SELECT 
    p.Id AS ParentId,
    SUM(rp.Amount) AS TotalAmount
FROM Pools p
LEFT JOIN RecursivePools rp 
    ON p.Id = rp.ParentId 
    AND rp.IsTerminating = 1
GROUP BY p.Id
ORDER BY p.Id;

逻辑说明 #

1. 锚点成员：标记每个节点是否为终止节点（IsTerminating=1表示该节点有Amount，其后代无需被上级父节点统计）。
2. 递归成员：仅当父节点无Amount时，才将当前节点的关联向上延伸至父节点的父节点，避免终止节点的后代被更高层父节点计入。
3. 汇总查询：通过左连接关联父节点与所有关联到它的终止节点，求和得到符合规则的总金额。

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