如何在SQL中统计树形结构各节点的子节点数量

Hey there, to get the total number of child nodes (including all nested descendants) for each node in your tree, you’ll want to use a recursive Common Table Expression (CTE)—this is the standard, clean way to handle hierarchical data in SQL.

Based on your table structures, here are tailored solutions for both of your tables:

Using the Organization_structure table #

This query traverses every node’s entire subtree and counts all descendants, matching your expected output:

WITH RECURSIVE node_descendants AS (
    -- Anchor: Grab all direct parent-child pairs first
    SELECT 
        parentID AS parent_node,
        orgID AS child_node
    FROM Organization_structure
    UNION ALL
    -- Recursive step: Keep adding nested children until no more exist
    SELECT 
        nd.parent_node,
        s.orgID AS child_node
    FROM node_descendants nd
    JOIN Organization_structure s ON nd.child_node = s.parentID
)
-- Aggregate to get total descendant count per parent node
SELECT 
    parent_node AS ID,
    COUNT(child_node) AS COUNT
FROM node_descendants
GROUP BY parent_node
ORDER BY ID;

Using the Organization_detail table #

If your primary tree data lives here (with ParentOrganizationID = 'top' as the root), adjust the query like this:

WITH RECURSIVE node_descendants AS (
    SELECT 
        ParentOrganizationID AS parent_node,
        OrganizationID AS child_node
    FROM Organization_detail
    WHERE ParentOrganizationID != 'top' -- Skip the root's placeholder parent
    UNION ALL
    SELECT 
        nd.parent_node,
        d.OrganizationID AS child_node
    FROM node_descendants nd
    JOIN Organization_detail d ON nd.child_node = d.ParentOrganizationID
)
SELECT 
    parent_node AS ID,
    COUNT(child_node) AS COUNT
FROM node_descendants
GROUP BY parent_node
ORDER BY ID;

Quick Notes: #

• This works in all modern SQL databases (PostgreSQL, MySQL 8.0+, SQL Server, Oracle 11g+).
• If you want to include nodes with zero children (showing a count of 0), left-join the CTE with your original nodes table and use COALESCE(COUNT(...), 0) to replace nulls with 0.

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