SQL Server 2016中用递归CTE统计上下线金额总和与记录数

实现思路 #

使用递归CTE（公共表表达式）遍历每个上线节点的所有层级下线（包含节点自身的记录，若存在），最后分组汇总每个上线对应的总金额和记录数。

示例代码 #

假设数据存储在表SalesData中，表结构为ST_ID（节点ID）、UPLINE（上线ID）、AMOUNT（金额），执行以下SQL：

WITH AllUplines AS (
    -- 提取所有存在的上线节点
    SELECT DISTINCT UPLINE AS UplineID
    FROM SalesData
),
RecursiveDownline AS (
    -- 锚点成员：获取每个上线节点自身的记录（如果该节点有数据）
    SELECT 
        au.UplineID AS ParentID,
        sd.ST_ID,
        sd.AMOUNT,
        1 AS RecordCount
    FROM AllUplines au
    LEFT JOIN SalesData sd ON au.UplineID = sd.ST_ID
    WHERE sd.ST_ID IS NOT NULL

    UNION ALL

    -- 递归成员：遍历当前节点的所有直接下线，逐层向下递归
    SELECT 
        rd.ParentID,
        sd.ST_ID,
        sd.AMOUNT,
        1 AS RecordCount
    FROM RecursiveDownline rd
    JOIN SalesData sd ON rd.ST_ID = sd.UPLINE
)
-- 汇总每个上线的累计金额与记录总数
SELECT 
    ParentID AS UPLINE,
    SUM(AMOUNT) AS AMOUNT,
    SUM(RecordCount) AS CNT
FROM RecursiveDownline
GROUP BY ParentID
ORDER BY ParentID;

代码说明 #

1. AllUplines CTE：先从原表中提取所有作为上线的节点ID，避免遗漏任何需要统计的上线。
2. RecursiveDownline CTE：
   • 锚点部分：关联上线节点自身的记录（如果该节点在ST_ID中存在，即自身有金额数据），作为递归的起始点。
   • 递归部分：将当前节点的ST_ID作为新的上线ID，关联其直接下线的记录，不断向下遍历，直到没有更多层级的下线为止。
3. 最终汇总：按上线节点分组，计算总金额和记录数。

验证结果 #

执行后将得到与预期一致的输出：

• 52001的累计金额为432+400+300=1132，记录数为3（对应自身+2个直接下线）
• 9024的累计金额为432+400+300+100=1232，记录数为4（对应2个直接下线+2个间接下线）
• 35119的累计金额为234+213=447，记录数为2（2个直接下线）
• 23141的累计金额为654，记录数为1（自身的直接下线）

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