如何用SQL循环+临时表递归获取指定父节点的全链路关联数据？

需求说明 #

从存储父子关系的表中，获取指定父节点（如A1）的全链路关联数据——从初始父节点开始，递归查询所有层级的子节点，直至无后续关联数据。

示例表结构与数据 #

预期结果（查询A1） #

最优解决方案：递归CTE（Common Table Expression） #

递归CTE是SQL处理层级数据的标准方案，性能优于临时表循环，语法简洁且无需手动管理循环逻辑，支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库。

实现代码 #

WITH RECURSIVE hierarchy AS (
    -- 锚点成员：获取初始父节点的直接子节点
    SELECT 
        `Parent ID`, 
        `Child ID`, 
        `Order Number`
    FROM 
        your_table_name
    WHERE 
        `Parent ID` = 'A1'
    
    UNION ALL
    
    -- 递归成员：以上一轮的Child ID作为Parent ID，查询下一层级数据
    SELECT 
        t.`Parent ID`, 
        t.`Child ID`, 
        t.`Order Number`
    FROM 
        your_table_name t
    JOIN 
        hierarchy h ON t.`Parent ID` = h.`Child ID`
)
SELECT * FROM hierarchy;

关键说明 #

• 锚点成员：定义递归起点，即指定父节点的直接子节点。
• 递归成员：通过JOIN关联上一轮结果，自动将子节点作为新父节点查询后续数据，当无新数据返回时自动终止递归。
• 无需手动标记已查询节点，CTE会自动处理重复与终止逻辑。

临时表循环实现方案（兼容低版本SQL） #

如果你的数据库不支持递归CTE（如MySQL 5.x），可以采用临时表+循环的方式实现：

1. 创建临时表并插入初始数据 #

-- 创建临时表存储全链路数据，主键避免重复插入
CREATE TEMPORARY TABLE temp_hierarchy (
    `Parent ID` VARCHAR(10),
    `Child ID` VARCHAR(10),
    `Order Number` INT,
    PRIMARY KEY (`Parent ID`, `Child ID`)
);

-- 插入初始父节点的直接子节点
INSERT INTO temp_hierarchy
SELECT `Parent ID`, `Child ID`, `Order Number`
FROM your_table_name
WHERE `Parent ID` = 'A1';

2. 循环查询并插入子节点数据 #

-- 定义变量记录每次插入的行数
SET @row_count = 1;

-- 循环直至无新数据插入
WHILE @row_count > 0 DO
    -- 插入下一层级数据：以临时表中的Child ID作为Parent ID查询
    INSERT INTO temp_hierarchy
    SELECT t.`Parent ID`, t.`Child ID`, t.`Order Number`
    FROM your_table_name t
    JOIN temp_hierarchy h ON t.`Parent ID` = h.`Child ID`
    -- 排除已存在的数据，避免重复
    WHERE NOT EXISTS (
        SELECT 1 FROM temp_hierarchy 
        WHERE `Parent ID` = t.`Parent ID` AND `Child ID` = t.`Child ID`
    );
    
    -- 更新插入行数，判断是否继续循环
    SET @row_count = ROW_COUNT();
END WHILE;

3. 查询最终结果 #

SELECT * FROM temp_hierarchy;

关键说明 #

• 用主键或NOT EXISTS条件避免重复插入，相当于标记已处理的节点关系。
• 通过ROW_COUNT()判断每次插入的行数，当为0时终止循环。

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