如何创建递归计数器统计各DealID的父级与子级数量？

我有两张表：关系表（t_parent）与交易表。交易表中存有一系列DealID，需要为每个DealID统计对应的父级数量（ParentCNT）和子级数量（ChildCNT）。

预期结果 #

当前未完成代码 #

WITH rec4 (ParentID, ChildID)
AS 
(
    SELECT ParentID, ChildID
    FROM t_parent 
 -- where ChildID = 106
    UNION ALL
    SELECT p.ParentID, p.ChildID
    FROM t_parent AS p
        JOIN rec4 AS r ON p.ParentID = r.ChildID

), rec5 (ParentID, ChildID)
AS 
(
    SELECT ParentID, ChildID
    FROM t_parent 
 -- where ChildID = 106
    UNION ALL
    SELECT p.ParentID, p.ChildID
    FROM t_parent AS p
        JOIN rec5 AS r ON p.ChildID = r.ParentID
)
--select ISNULL(a.DealID, 0), ttt.ParentCNT, ttt.ChildCNT
  --from #A a left join
  select ch.ParentID, ChildCNT, ParentCNT from (
select ParentID, COUNT(r5.ChildID) as ChildCNT from rec5 r5 Group by ParentID) ch
join (select ChildID, COUNT(r4.ParentID) as ParentCNT from rec4 r4 Group by ChildID) pr
on ch.ParentID = pr.ChildID--) ttt
--on a.DealID = ttt.ParentID

修正后的实现代码 #

要达成需求，需分别递归遍历每个DealID的所有上级父节点和下级子节点，再关联交易表完成统计，同时处理无父/子节点时的0值：

-- 假设交易表名为t_deals，包含DealID字段
WITH ParentRecursion AS (
    -- 基础节点：关联DealID对应的直接父节点
    SELECT 
        d.DealID,
        p.ParentID AS AncestorID
    FROM t_deals d
    LEFT JOIN t_parent p ON d.DealID = p.ChildID
    UNION ALL
    -- 递归向上查找所有层级的父节点
    SELECT 
        pr.DealID,
        p.ParentID AS AncestorID
    FROM ParentRecursion pr
    JOIN t_parent p ON pr.AncestorID = p.ChildID
),
ChildRecursion AS (
    -- 基础节点：关联DealID对应的直接子节点
    SELECT 
        d.DealID,
        p.ChildID AS DescendantID
    FROM t_deals d
    LEFT JOIN t_parent p ON d.DealID = p.ParentID
    UNION ALL
    -- 递归向下查找所有层级的子节点
    SELECT 
        cr.DealID,
        p.ChildID AS DescendantID
    FROM ChildRecursion cr
    JOIN t_parent p ON cr.DescendantID = p.ParentID
)
-- 统计每个DealID的父级、子级数量
SELECT 
    d.DealID,
    COUNT(DISTINCT pr.AncestorID) AS ParentCNT,
    COUNT(DISTINCT cr.DescendantID) AS ChildCNT
FROM t_deals d
LEFT JOIN ParentRecursion pr ON d.DealID = pr.DealID
LEFT JOIN ChildRecursion cr ON d.DealID = cr.DealID
GROUP BY d.DealID
ORDER BY d.DealID;

代码说明 #

1. ParentRecursion：递归向上遍历每个DealID的所有祖先父节点，覆盖所有层级。
2. ChildRecursion：递归向下遍历每个DealID的所有后代子节点，覆盖所有层级。
3. 最终通过LEFT JOIN关联交易表，使用COUNT(DISTINCT)避免重复计数；无父/子节点的DealID会自动返回0值，符合预期结果要求。

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