SQL - 递归查询父节点

表a中有id, parent_id两个字段，parent_id存id的上一级id。请帮我写一个SQL语句，递归查询出一个id的所有parent_id，输出为一行数据多个列。

 

ChatGPT said:

非常好的问题 👍
这是一个典型的树形结构递归查询，你希望从一个 id 出发，逐级向上找所有父节点（parent_id），并且把这些父节点展开成一行多列。

下面我分别给出 PostgreSQL / Greenplum / MySQL 8+ 的写法。

---

🟢 以 PostgreSQL / Greenplum 为例

假设表结构如下：

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

 

你要查某个节点（比如 id = 10）的所有父节点：

🔹 SQL：

WITH RECURSIVE parent_tree AS (
  SELECT id, parent_id, 1 AS level
  FROM a
  WHERE id = 10

  UNION ALL

  SELECT a.id, a.parent_id, pt.level + 1
  FROM a
  JOIN parent_tree pt ON a.id = pt.parent_id
)
SELECT 
  MAX(CASE WHEN level = 1 THEN id END) AS level1_id,
  MAX(CASE WHEN level = 2 THEN id END) AS level2_id,
  MAX(CASE WHEN level = 3 THEN id END) AS level3_id,
  MAX(CASE WHEN level = 4 THEN id END) AS level4_id
FROM parent_tree;

 

🧠 说明：

• WITH RECURSIVE 从目标 id 开始，不断向上关联 a.id = parent_tree.parent_id。

• 递归层数用 level 控制。

• 最后用 CASE WHEN+聚合函数 MAX() 将多行“旋转”为一行多列。

✅ 结果示例：

level1_id	level2_id	level3_id	level4_id
10	7	3	1

---

🟡 如果不知道最大层级怎么办？

可以自动生成列名的动态 SQL：

DO $$
DECLARE
  sql TEXT;
BEGIN
  SELECT string_agg(
    format('MAX(CASE WHEN level = %s THEN id END) AS level%s_id', level, level),
    ', '
  )
  INTO sql
  FROM generate_series(1, 10) AS level;  -- 假设最大10层

  sql := format('
    WITH RECURSIVE parent_tree AS (
      SELECT id, parent_id, 1 AS level FROM a WHERE id = 10
      UNION ALL
      SELECT a.id, a.parent_id, pt.level + 1
      FROM a JOIN parent_tree pt ON a.id = pt.parent_id
    )
    SELECT %s FROM parent_tree;', sql);

  EXECUTE sql;
END $$;

 

---

🔵 MySQL 8.0+ 写法（略有不同）

MySQL 8 也支持递归 CTE：

WITH RECURSIVE parent_tree AS (
  SELECT id, parent_id, 1 AS level
  FROM a
  WHERE id = 10
  UNION ALL
  SELECT a.id, a.parent_id, pt.level + 1
  FROM a
  JOIN parent_tree pt ON a.id = pt.parent_id
)
SELECT
  MAX(CASE WHEN level = 1 THEN id END) AS level1_id,
  MAX(CASE WHEN level = 2 THEN id END) AS level2_id,
  MAX(CASE WHEN level = 3 THEN id END) AS level3_id,
  MAX(CASE WHEN level = 4 THEN id END) AS level4_id
FROM parent_tree;

 

---

如果你告诉我：

• 你使用的是哪种数据库（PostgreSQL / Greenplum / MySQL / Oracle / SQL Server 等）

• 最大层级大约是多少（例如 <=10）

我可以帮你写出一个完全可执行的版本，包括动态列生成的方案。

要我帮你生成对应数据库的最终版吗？

 

You said:

MySQL

 

ChatGPT said:

非常好 👍 那我们来写 MySQL 8.0+ 版本的完整可执行 SQL。

你的需求是：

从表 a(id, parent_id) 中递归查找某个节点的所有上级 parent_id，
并把结果输出成“一行多列”（例如 parent1, parent2, parent3...）。

---

✅ 一、示例表结构与数据

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

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

 

层级结构示意：

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

 

---

✅ 二、递归查询：从某个 id 往上找所有父节点

比如要查 id = 6 的所有父级：

WITH RECURSIVE parent_tree AS (
  SELECT id, parent_id, 1 AS level
  FROM a
  WHERE id = 6
  UNION ALL
  SELECT a.id, a.parent_id, pt.level + 1
  FROM a
  JOIN parent_tree pt ON a.id = pt.parent_id
)
SELECT * FROM parent_tree;

 

输出：

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

---

✅ 三、把父节点展开为“一行多列”

我们可以用 MAX(CASE WHEN level=... THEN parent_id END) 转置：

WITH RECURSIVE parent_tree AS (
  SELECT id, parent_id, 1 AS level
  FROM a
  WHERE id = 6
  UNION ALL
  SELECT a.id, a.parent_id, pt.level + 1
  FROM a
  JOIN parent_tree pt ON a.id = pt.parent_id
)
SELECT 
  MAX(CASE WHEN level = 2 THEN id END) AS parent_1,
  MAX(CASE WHEN level = 3 THEN id END) AS parent_2,
  MAX(CASE WHEN level = 4 THEN id END) AS parent_3,
  MAX(CASE WHEN level = 5 THEN id END) AS parent_4
FROM parent_tree;

 

结果：

parent_1	parent_2	parent_3	parent_4
4	3	2	1

---

✅ 四、如果不确定层级深度（动态列）

MySQL 不支持动态列生成的 pivot，因此可以用 存储过程 动态生成 SQL：

DELIMITER //

CREATE PROCEDURE get_parents(IN start_id INT)
BEGIN
  DECLARE max_level INT DEFAULT 10;  -- 假设最多10层
  DECLARE sql_text TEXT;

  SET @base_cte = '
    WITH RECURSIVE parent_tree AS (
      SELECT id, parent_id, 1 AS level
      FROM a
      WHERE id = ';

  SET @rest_cte = '
      UNION ALL
      SELECT a.id, a.parent_id, pt.level + 1
      FROM a
      JOIN parent_tree pt ON a.id = pt.parent_id
    )
    SELECT ';

  SELECT GROUP_CONCAT(
           CONCAT('MAX(CASE WHEN level = ', n, ' THEN id END) AS parent_', n - 1)
           ORDER BY n SEPARATOR ', '
         )
  INTO @cols
  FROM (
    SELECT 2 AS n UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
  ) t;

  SET sql_text = CONCAT(@base_cte, start_id, @rest_cte, @cols, ' FROM parent_tree;');

  PREPARE stmt FROM sql_text;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

 

调用：

 

CALL get_parents(6);

输出：

parent_1	parent_2	parent_3	parent_4
4	3	2	1

---

✅ 总结

目标	解决方案
递归查找所有上级	WITH RECURSIVE
输出多行转多列	MAX(CASE WHEN level=...)
动态层级	存储过程生成动态 SQL