如何用SQL查询父节点的所有后代（含后代的后代）？

核心逻辑 #

现有数据集只有节点和层级，没有直接的父节点关联，所以需要靠层级差和节点的出现顺序来推导父子关系：

• Level 1的节点（A）的直接子节点是所有Level 2的节点（B、E），同时要把Level 3的节点（C、D）作为A的间接子节点纳入
• Level 2的节点（B）的子节点是它之后、下一个Level 2节点（E）之前的所有Level 3节点（C、D）

具体SQL实现（以MySQL为例） #

假设你的原始表名为node_level，且数据是按你给出的顺序存储的（A→B→C→D→E），可以用CTE（公共表表达式）来实现：

WITH numbered_nodes AS (
    -- 给每个节点按顺序加行号，用来界定B的后代范围
    SELECT 
        Node, 
        Level,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM node_level
),
level2_ranges AS (
    -- 找到每个Level2节点的起始和结束行号（结束行号是下一个Level2节点的行号）
    SELECT 
        Node AS parent_node,
        rn AS start_rn,
        LEAD(rn) OVER (ORDER BY rn) AS end_rn
    FROM numbered_nodes
    WHERE Level = 2
)
-- 合并所有父子关系
SELECT 'A' AS Parent, Node AS Child FROM numbered_nodes WHERE Level = 2
UNION ALL
SELECT 'A' AS Parent, Node AS Child FROM numbered_nodes WHERE Level = 3
UNION ALL
SELECT lr.parent_node AS Parent, nn.Node AS Child
FROM level2_ranges lr
JOIN numbered_nodes nn 
  ON nn.rn > lr.start_rn 
  AND (lr.end_rn IS NULL OR nn.rn < lr.end_rn)
WHERE nn.Level = 3
ORDER BY Parent, Child;

代码说明 #

1. numbered_nodes：给每个节点生成行号，确保能准确定位B和E的位置，从而界定B的后代范围
2. level2_ranges：获取每个Level2节点的行号区间，比如B的起始行号是2，结束行号是5（E的行号），所以B的后代是行号3、4的节点（C、D）
3. 三个SELECT分别生成：
   • A的直接子节点（B、E）
   • A的间接子节点（C、D）
   • B的直接子节点（C、D）
4. 最后用UNION ALL合并结果并排序，得到你需要的Parent-Child表

关于存储过程 #

如果需要重复使用这个逻辑，可以把上述SQL封装成存储过程，示例如下：

DELIMITER //
CREATE PROCEDURE GenerateParentChildTable()
BEGIN
    WITH numbered_nodes AS (
        SELECT 
            Node, 
            Level,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
        FROM node_level
    ),
    level2_ranges AS (
        SELECT 
            Node AS parent_node,
            rn AS start_rn,
            LEAD(rn) OVER (ORDER BY rn) AS end_rn
        FROM numbered_nodes
        WHERE Level = 2
    )
    SELECT 'A' AS Parent, Node AS Child FROM numbered_nodes WHERE Level = 2
    UNION ALL
    SELECT 'A' AS Parent, Node AS Child FROM numbered_nodes WHERE Level = 3
    UNION ALL
    SELECT lr.parent_node AS Parent, nn.Node AS Child
    FROM level2_ranges lr
    JOIN numbered_nodes nn 
      ON nn.rn > lr.start_rn 
      AND (lr.end_rn IS NULL OR nn.rn < lr.end_rn)
    WHERE nn.Level = 3
    ORDER BY Parent, Child;
END //
DELIMITER ;

调用时只需执行：CALL GenerateParentChildTable();

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