如何统计Oracle树形结构中所有顶层父节点的子节点总数？

表结构与样例数据 #

现有Oracle树形结构表Hierarchy_Tree，表结构为(PARENT, CHILD)，样例数据如下：

CHILD   PARENT  
-------------------------
AA          A                   
AB          A                   
AAA         AA                   
BB          B                   
BBB         BB                   
BBBA        BBB                  
BBBB        BBB  
C1           C
C2           C
C3           C
C4           C3
C5           C3
C6           C3
C7           C6
C8           C6

需求 #

编写SQL查询，仅返回顶层父节点（无上级节点的节点）及其所有层级子节点的总数，预期输出如下：

PARENT   COUNT
----------------------------
A       3
B       4  
C       8

错误尝试分析 #

你尝试的SQL存在两个核心问题：

1. 递归方向错误：connect by nocycle parent = prior child是向上递归查找父节点，而非向下遍历子节点，统计逻辑完全偏离需求。
2. 未指定顶层父节点作为递归起点：connect_by_root(child)拿到的是各个子节点本身，无法关联到真正的顶层父节点。

select child1, count(*)-1 as "RESULT COUNT"
  from (
    select connect_by_root(child) child1
    from Hierarchy_Tree
    connect by nocycle parent = prior child
    )
group by child1
order by 1 asc

正确实现方案 #

方案1：基于CONNECT BY的递归统计 #

SELECT top_parent AS PARENT, COUNT(*) AS COUNT
FROM (
    -- 递归遍历每个顶层父节点的所有子节点，标记所属顶层父节点
    SELECT CONNECT_BY_ROOT ht.PARENT AS top_parent
    FROM Hierarchy_Tree ht
    -- 筛选顶层父节点：不存在于CHILD列中的PARENT（无上级节点）
    START WITH ht.PARENT IN (
        SELECT DISTINCT PARENT
        FROM Hierarchy_Tree
        WHERE PARENT NOT IN (SELECT CHILD FROM Hierarchy_Tree)
    )
    -- 向下递归：当前节点的CHILD是下一级节点的PARENT
    CONNECT BY PRIOR ht.CHILD = ht.PARENT
)
GROUP BY top_parent
ORDER BY top_parent;

方案2：使用WITH递归子句（Oracle 11gR2+支持） #

如果你的Oracle版本支持WITH递归，可以用更直观的写法：

WITH recursive_tree AS (
    -- 初始层：顶层父节点及其直接子节点
    SELECT PARENT AS top_parent, CHILD
    FROM Hierarchy_Tree
    WHERE PARENT NOT IN (SELECT CHILD FROM Hierarchy_Tree)
    UNION ALL
    -- 递归层：遍历所有子节点
    SELECT rt.top_parent, ht.CHILD
    FROM recursive_tree rt
    JOIN Hierarchy_Tree ht ON rt.CHILD = ht.PARENT
)
SELECT top_parent AS PARENT, COUNT(*) AS COUNT
FROM recursive_tree
GROUP BY top_parent
ORDER BY top_parent;

逻辑说明 #

1. 顶层父节点识别：通过PARENT NOT IN (SELECT CHILD FROM Hierarchy_Tree)筛选出没有上级节点的顶层节点（A、B、C）。
2. 递归遍历：从顶层节点出发，向下遍历所有层级的子节点，每个子节点都标记所属的顶层父节点。
3. 统计总数：按顶层父节点分组，统计对应的子节点总数，得到预期结果。

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