SQL - 递归查询子节点

递归查询子节点：表a中有id, parent_id两个字段，parent_id存id的上一级id。请帮我写一个SQL语句，给定一个id，递归查询出所有子id，该id为第一列，后面每有一个子id增加一列。

 

非常好，✅你的意思是：

• 给定一个 id，递归查询出所有下级节点（所有子孙）；

• 结果可以是多行输出；

• 每一行表示从根到当前节点的一条路径；

• 每一级节点占用一列。

例如表 a(id, parent_id) 内容如下：

id	parent_id
1	NULL
2	1
3	1
4	2
5	2
6	3
7	4

结构是：

1
├─ 2
│  ├─ 4
│  │  └─ 7
│  └─ 5
└─ 3
   └─ 6

 

---

🎯 目标：给定 id = 1，希望结果是如下形式（多行，每行表示一条完整路径）：

level1	level2	level3	level4
1	2	4	7
1	2	5	NULL
1	3	6	NULL

---

✅ MySQL 8.0+ 实现方案

MySQL 8.0 支持 递归 CTE，我们可以利用它自顶向下展开路径。

WITH RECURSIVE cte AS (
    -- 初始节点
    SELECT 
        id,
        parent_id,
        CAST(id AS CHAR(200)) AS path   -- 保存路径字符串
    FROM a
    WHERE id = 1   -- 起始id

    UNION ALL

    -- 递归部分：连接子节点
    SELECT 
        a.id,
        a.parent_id,
        CONCAT(cte.path, ',', a.id) AS path
    FROM a
    JOIN cte ON a.parent_id = cte.id
)
SELECT 
    path
FROM cte
ORDER BY path;

 

结果：

path
---------
1
1,2
1,2,4
1,2,4,7
1,2,5
1,3
1,3,6

 

---

✅ 将路径展开为多列（用字符串拆分）

MySQL 本身不支持直接按分隔符拆分多列，但可以用 JSON 函数巧妙实现：

WITH RECURSIVE cte AS (
    SELECT 
        id,
        parent_id,
        JSON_ARRAY(id) AS path
    FROM a
    WHERE id = 1

    UNION ALL

    SELECT 
        a.id,
        a.parent_id,
        JSON_ARRAY_APPEND(cte.path, '$', a.id)
    FROM a
    JOIN cte ON a.parent_id = cte.id
)
SELECT
    JSON_EXTRACT(path, '$[0]') AS level1,
    JSON_EXTRACT(path, '$[1]') AS level2,
    JSON_EXTRACT(path, '$[2]') AS level3,
    JSON_EXTRACT(path, '$[3]') AS level4
FROM cte
ORDER BY path;

 

结果：

level1	level2	level3	level4
1	2	4	7
1	2	5	NULL
1	3	6	NULL

---

✅ 可改动点

• 如果最大层级不确定，可以动态拼列（用 INFORMATION_SCHEMA.COLUMNS 拼 SQL 实现）。

• 你也可以把 id = 1 换成任意起点，递归会自动找出它的所有子孙路径。

---

是否希望我帮你扩展成一个 动态列版本（自动检测最大深度并展开相应列） 的完整 SQL？

 

You said:

好的，谢谢。

 

ChatGPT said:

非常好 👍 那我们就来写一个 动态列版本 —— 它会自动计算最大层级，然后动态生成 SQL 输出每层一个列。

---

✅ 目标总结

给定表结构：

CREATE TABLE a (
    id INT PRIMARY KEY,
    parent_id INT
);

 

要求：

• 输入一个起点 ID（例如 1）；

• 递归查询所有下级；

• 每一行代表从起点到一个节点的完整路径；

• 自动生成合适数量的列（level1, level2, level3, ...）。

---

✅ 实现方案（MySQL 8.0+）

这是一个 动态 SQL 实现。

一、示例数据

INSERT INTO a (id, parent_id) VALUES
(1, NULL),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 3),
(7, 4);

 

---

二、动态 SQL 实现代码

-- 变量：起始ID
SET @start_id = 1;

-- Step 1: 递归构建所有路径（用 JSON 数组保存）
WITH RECURSIVE cte AS (
    SELECT 
        id,
        parent_id,
        JSON_ARRAY(id) AS path
    FROM a
    WHERE id = @start_id

    UNION ALL

    SELECT 
        a.id,
        a.parent_id,
        JSON_ARRAY_APPEND(cte.path, '$', a.id)
    FROM a
    JOIN cte ON a.parent_id = cte.id
)
SELECT * INTO TEMPORARY TABLE tmp_paths FROM cte;

-- Step 2: 找出最大深度
SELECT MAX(JSON_LENGTH(path)) INTO @max_depth FROM tmp_paths;

-- Step 3: 拼接动态SQL
SET @sql = 'SELECT ';
SET @i = 0;

WHILE @i < @max_depth DO
  SET @sql = CONCAT(@sql,
    CASE WHEN @i > 0 THEN ', ' ELSE '' END,
    'JSON_UNQUOTE(JSON_EXTRACT(path, ''$[', @i, ']'')) AS level', @i + 1
  );
  SET @i = @i + 1;
END WHILE;

SET @sql = CONCAT(@sql, ' FROM tmp_paths ORDER BY path;');

-- Step 4: 执行生成的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

---

三、执行结果（给定 @start_id = 1）

level1	level2	level3	level4
1	2	4	7
1	2	5	NULL
1	3	6	NULL

---

四、说明

✅ 特点：

• 自动检测最大深度；

• 动态生成列数；

• 每行表示从起始节点到某节点的一条完整路径；

• 支持任意层级树结构。

⚠️ 注意：

• 仅支持 MySQL 8.0+；

• 执行中创建了临时表 tmp_paths，可改名；

• 如果路径数量很多，可在 Step 1 的 cte 结果中加上 WHERE 限制防止过大递归。