父子结构递归SQL查询新增每条记录总下线数实现方案咨询

最优方案是通过双递归CTE实现，既保留原有层级路径、排序逻辑，又能高效统计每个节点的所有下级人数：

WITH 
-- 原有生成层级、路径的CTE
base_cte AS (
    SELECT 
        PLID, sponsorid, firstname, lastname, Status, 
        0 AS LEVEL, 
        CAST(firstname AS VARCHAR(1000)) AS path
    FROM TEST  
    WHERE PLID =1  
    UNION ALL
    SELECT 
        c.PLID, c.sponsorid, c.firstname, c.lastname, c.Status, 
        base_cte.LEVEL + 1 AS LEVEL,
        CAST((base_cte.path + '/' + c.firstname) AS VARCHAR(1000)) AS path 
    FROM TEST c 
    INNER JOIN base_cte ON c.sponsorid = base_cte.plid
),
-- 新增统计每个节点下属数量的CTE
downline_count_cte AS (
    -- 锚点：每个节点作为根节点
    SELECT PLID AS root_plid, PLID AS current_plid FROM TEST
    UNION ALL
    -- 递归找根节点的所有后代
    SELECT d.root_plid, t.PLID 
    FROM downline_count_cte d
    INNER JOIN TEST t ON t.sponsorid = d.current_plid
)
-- 关联两个CTE得到最终结果
SELECT 
    b.*,
    -- 减去1是排除节点自身，没有下属的返回0
    ISNULL(COUNT(d.current_plid) -1, 0) AS TotalDownline
FROM base_cte b
LEFT JOIN downline_count_cte d ON b.PLID = d.root_plid
GROUP BY b.PLID, b.sponsorid, b.firstname, b.lastname, b.Status, b.LEVEL, b.path
ORDER BY b.path ASC

逻辑说明 #

• base_cte完全复用原有逻辑，保留层级、路径、排序规则
• downline_count_cte通过递归遍历，为每个节点匹配到所有归属它的后代节点
• 最终关联统计时，减去节点自身的计数，就得到所有下级的总人数，空值用ISNULL处理为0，输出结果和预期完全一致。

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