如何在SQL中获取层级数据指定节点的所有子节点？

针对你这个树形结构节点遍历的需求，我来分享几种实用的解决方案，适配不同的SQL数据库环境：

首先先明确你的表结构和示例数据，方便后续测试：

CREATE TABLE your_table (
    id INT PRIMARY KEY,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES your_table(id)
);

-- 插入你提供的示例数据
INSERT INTO your_table (id, parent_id) VALUES
(1, NULL),
(2, 1),
(3, 4),
(4, 8),
(5, 1),
(6, 2),
(7, 6),
(8, NULL);

方法一：递归CTE（推荐，适用于现代数据库） #

这是目前最通用也最清晰的实现方式，支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11gR2+等遵循SQL:1999标准的数据库。

递归CTE分为两部分：锚点成员（选中目标节点本身）和递归成员（循环遍历所有子节点）。

查询指定节点的所有子节点（含自身） #

WITH RECURSIVE node_hierarchy AS (
    -- 锚点：选中目标节点
    SELECT id
    FROM your_table
    WHERE id = 1 -- 替换成你要查询的节点ID
    UNION ALL
    -- 递归：找到所有子节点
    SELECT t.id
    FROM your_table t
    JOIN node_hierarchy nh ON t.parent_id = nh.id
)
SELECT id FROM node_hierarchy ORDER BY id;

• 当查询id=1时，结果为：1, 2, 5, 6, 7，完全符合你的需求。
• 当查询id=8时，根据你的表数据，实际结果是8, 4, 3（因为4的父节点是8，3的父节点是4），你提到的8,4,2应该是数据录入有误哦~

方法二：自定义函数（适用于不支持递归的旧版数据库） #

如果你的数据库版本较低（比如MySQL 5.x），不支持递归CTE，可以用自定义函数来循环遍历子节点：

创建函数 #

DELIMITER //
CREATE FUNCTION get_all_child_nodes(root_id INT)
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
    DECLARE child_nodes VARCHAR(1000);
    DECLARE temp_nodes VARCHAR(1000);
    
    -- 初始化：先加入目标节点本身
    SET child_nodes = CAST(root_id AS CHAR);
    SET temp_nodes = CAST(root_id AS CHAR);
    
    -- 循环查找所有子节点，直到没有新节点为止
    WHILE temp_nodes IS NOT NULL DO
        SET temp_nodes = (
            SELECT GROUP_CONCAT(id SEPARATOR ',') 
            FROM your_table 
            WHERE FIND_IN_SET(parent_id, temp_nodes) 
            AND NOT FIND_IN_SET(id, child_nodes)
        );
        IF temp_nodes IS NOT NULL THEN
            SET child_nodes = CONCAT(child_nodes, ',', temp_nodes);
        END IF;
    END WHILE;
    
    RETURN child_nodes;
END //
DELIMITER ;

调用函数 #

• 获取逗号分隔的子节点列表：

SELECT get_all_child_nodes(1) AS child_nodes;

• 如果需要拆分成单独的行（MySQL示例）：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(child_nodes, ',', numbers.n), ',', -1) AS id
FROM (SELECT get_all_child_nodes(1) AS child_nodes) t
-- 这里的数字序列要覆盖可能的最大子节点数量，可按需扩展
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers
ON CHAR_LENGTH(child_nodes) - CHAR_LENGTH(REPLACE(child_nodes, ',', '')) >= numbers.n - 1;

方法三：Oracle专属的CONNECT BY语法 #

如果你使用Oracle数据库，可以用原生的CONNECT BY语法来实现：

SELECT id
FROM your_table
START WITH id = 1 -- 目标节点ID
CONNECT BY PRIOR id = parent_id
ORDER BY id;

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