SQL Server层级节点金额计算：查询节点自身+直接子节点计算金额总和

我有一张SQL Server表[_TestPoolCalc]，需要实现查询结果展示每个节点的PoolID、Amount，以及该节点自身Amount加上其所有直接子节点Calculated Amount的总和，预期输出见下文。

表脚本与测试数据 #

CREATE TABLE [dbo].[_TestPoolCalc](
    [PoolID] [varchar](50) NULL,
    [ParentPoolID] [varchar](50) NULL,
    [Amount] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 1', N'ROOT', NULL)
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 1.1', N'Pool 1', NULL)
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 1.1.1', N'Pool 1.1', NULL)
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 1.1.1.1', N'Pool 1.1.1', CAST(-12500.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 1.1.1.2', N'Pool 1.1.1', CAST(-12500.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 1.1.2', N'Pool 1.1', CAST(-25000.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 1.2', N'Pool 1', CAST(25000.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 1.3', N'Pool 1', CAST(-50000.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 2', N'ROOT', NULL)
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 2.1', N'Pool 2', NULL)
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 2.2', N'Pool 2', NULL)
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'Pool 2.3', N'Pool 2', CAST(75000.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[_TestPoolCalc] ([PoolID], [ParentPoolID], [Amount]) VALUES (N'ROOT', NULL, NULL)
GO

注：原测试数据中部分PoolID带有换行符，已统一去除以避免匹配错误

我的尝试方案 #

WITH p AS (SELECT            
   a.ParentPoolID, a.PoolID 
   , CAST(a.PoolID AS VARCHAR(MAX)) AS path    
   , len(CAST(a.PoolID AS VARCHAR(MAX))) lpath    
   , a.Amount    
   
  FROM _TestPoolCalc a    
  WHERE a.ParentPoolID = 'Root'    
        UNION ALL    
  SELECT    
   pp.ParentPoolID, pp.PoolID   
   , p_2.path + '>' + pp.PoolID AS path    
   , len(p_2.path + '>' + pp.PoolID) lpath    
   , pp.Amount   
   
  FROM _TestPoolCalc pp     
   
  JOIN p AS p_2 ON pp.ParentPoolID = p_2.PoolID 
  )    
SELECT PoolID,ParentPoolID, path    
,Amount
,isnull((select sum(isnull(p1.Amount,0.0)) from p p1 where left(p1.path,p.lpath) = p.path and p.poolid <> p1.PoolId ),0) CalculatedAmount     
FROM  p
order by path

预期输出 #

解决方案 #

要实现需求，需要自底向上的递归计算，因为每个节点的Calculated Amount依赖于其直接子节点的Calculated Amount。具体SQL语句如下：

WITH RecursivePool AS (
    -- 先处理叶子节点：没有子节点的节点，CalculatedAmount等于自身Amount（空则为0）
    SELECT 
        PoolID,
        ParentPoolID,
        Amount,
        ISNULL(Amount, 0.0) AS CalculatedAmount,
        CAST(PoolID AS VARCHAR(MAX)) AS Path
    FROM _TestPoolCalc p
    WHERE NOT EXISTS (
        SELECT 1 FROM _TestPoolCalc child 
        WHERE child.ParentPoolID = p.PoolID
    )
    UNION ALL
    -- 递归向上计算父节点：自身Amount加所有直接子节点的CalculatedAmount之和
    SELECT 
        parent.PoolID,
        parent.ParentPoolID,
        parent.Amount,
        ISNULL(parent.Amount, 0.0) + SUM(child.CalculatedAmount) AS CalculatedAmount,
        CAST(parent.PoolID AS VARCHAR(MAX)) + '>' + child.Path AS Path
    FROM _TestPoolCalc parent
    JOIN RecursivePool child ON parent.PoolID = child.ParentPoolID
    GROUP BY parent.PoolID, parent.ParentPoolID, parent.Amount
),
-- 生成序号和计算表达式的辅助CTE
ResultWithSeq AS (
    SELECT 
        PoolID,
        Amount,
        CalculatedAmount,
        Path,
        ROW_NUMBER() OVER(ORDER BY Path) AS Seq
    FROM RecursivePool
    WHERE PoolID <> 'ROOT'
)
SELECT 
    Seq AS '#',
    PoolID AS 'PoolID (A)',
    Amount AS 'Amount (B)',
    CONCAT(
        CAST(CalculatedAmount AS INT),
        ' `',
        CASE
            -- 有子节点的情况，拼接自身和子节点的表达式
            WHEN EXISTS(SELECT 1 FROM _TestPoolCalc child WHERE child.ParentPoolID = r.PoolID)
            THEN CONCAT('B', r.Seq, '+', STRING_AGG('C' + CAST(c.Seq AS VARCHAR), '+'))
            -- 叶子节点直接用自身Amount
            ELSE CONCAT('B', r.Seq)
        END,
        '`'
    ) AS 'Calculated Amount (C)'
FROM ResultWithSeq r
LEFT JOIN ResultWithSeq c ON r.PoolID = c.ParentPoolID
GROUP BY r.Seq, r.PoolID, r.Amount, r.CalculatedAmount, r.Path
ORDER BY r.Seq;

说明 #

• 递归CTE先处理叶子节点，再逐层向上计算父节点的Calculated Amount，确保子节点的值先被计算完成。
• 通过ROW_NUMBER()生成预期输出的序号，STRING_AGG()拼接计算表达式（如B1+C2+C7+C8）。
• 自动排除ROOT节点，符合预期输出的展示要求。

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