WITH RECURSIVE 函数

在 MySQL 中，可以使用 WITH RECURSIVE 语法来实现递归查询，这是处理具有层级结构数据（如组织结构、分类目录等）的强大工具。

WITH RECURSIVE 由两部分组成：

  1. 锚点成员（Anchor Member）：初始查询，返回递归的基础数据
  2. 递归成员（Recursive Member）：引用 CTE 名称，与锚点成员结果进行连接，直到没有新的结果返回

下面是一个使用 WITH RECURSIVE 处理层级结构数据的示例：

假设我们有一个部门表 departments，结构如下：

sql
 
 
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES departments(id)
);
 

如果要查询某个部门及其所有子部门（包括多级子部门），可以使用递归 CTE：

sql
 
 
WITH RECURSIVE department_hierarchy AS (
    -- 锚点成员：查询初始部门
    SELECT id, name, parent_id, 0 AS level
    FROM departments
    WHERE id = 1  -- 起始部门ID
    
    UNION ALL
    
    -- 递归成员：查询子部门
    SELECT d.id, d.name, d.parent_id, dh.level + 1 AS level
    FROM departments d
    INNER JOIN department_hierarchy dh ON d.parent_id = dh.id
)
-- 使用递归结果
SELECT id, name, parent_id, level
FROM department_hierarchy
ORDER BY level, id;
 

这个查询会：

  1. 首先从 ID 为 1 的部门开始（锚点成员）
  2. 然后递归查询所有 parent_id 等于上一级部门 ID 的部门
  3. 每递归一次，层级（level）加 1
  4. 直到没有更多的子部门为止

另一个常见用途是生成数字序列：

sql
 
 
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;
 

这个查询会生成 1 到 10 的数字序列。

使用 WITH RECURSIVE 时需要注意：

  • 必须使用 UNION ALL 连接锚点成员和递归成员
  • 递归成员必须包含对 CTE 自身的引用
  • 要确保递归能够终止，避免无限循环
  • MySQL 8.0 及以上版本才支持 WITH RECURSIVE 语法
 
WITH RECURSIVE 向上递归
 
WITH RECURSIVE 同样可以实现向上递归查询，即从某个节点开始，逐层向上查询其所有父节点（包括多级父节点），直到根节点（通常是 parent_id 为 NULL 或 0 的节点）。

以下是向上递归的示例：

假设仍使用前面的 departments 表，现在要查询 ID 为 5 的部门及其所有上级部门：

sql
 
 
WITH RECURSIVE department_ancestors AS (
    -- 锚点成员：查询起始部门
    SELECT id, name, parent_id, 0 AS level
    FROM departments
    WHERE id = 5  -- 起始部门ID
    
    UNION ALL
    
    -- 递归成员：查询父部门（向上递归）
    SELECT d.id, d.name, d.parent_id, da.level + 1 AS level
    FROM departments d
    INNER JOIN department_ancestors da ON d.id = da.parent_id
    -- 终止条件：当 parent_id 为 NULL 时停止（根节点没有父节点）
    WHERE da.parent_id IS NOT NULL
)
-- 使用递归结果
SELECT id, name, parent_id, level
FROM department_ancestors
ORDER BY level, id;
 

查询逻辑说明：

  1. 锚点成员先选中起始部门（ID=5）
  2. 递归成员通过 d.id = da.parent_id 关联，即查找当前部门的父部门
  3. 每向上递归一层，level 加 1（这里的 level 表示与起始部门的层级距离）
  4. 当某个节点的 parent_id 为 NULL 时（根节点），递归终止

应用场景：

  • 查找某个用户的所有上级领导
  • 查找某个分类的所有上级分类
  • 查找某个地区的所有上级地区（如区→市→省→国家）

注意：

  • 向上递归的关键是关联条件 d.id = da.parent_id（与向下递归相反）
  • 确保数据中没有循环引用（如 A 的父级是 B，B 的父级又是 A），否则会导致无限递归
  • 可以通过 WHERE 子句明确指定终止条件，使递归更可控
 
WITH RECURSIVE 向上向下递归
 
如果需要同时实现向上上下递归 **（即获取某个节点的所有祖先节点和所有后代节点），可以结合向上递归和向下递归的逻辑，通过两次递归查询或一次联合查询实现。

以下是同时获取某个节点的所有上级和下级节点的示例：

假设仍使用 departments 表（id, name, parent_id），现在要查询 ID=3 的部门的所有上级和下级：

sql
 
 
WITH RECURSIVE
-- 向上递归：获取所有祖先节点
ancestors AS (
    SELECT id, name, parent_id, 'ancestor' AS relation, 0 AS level
    FROM departments
    WHERE id = 3  -- 起始节点
    
    UNION ALL
    
    SELECT d.id, d.name, d.parent_id, 'ancestor', a.level + 1
    FROM departments d
    INNER JOIN ancestors a ON d.id = a.parent_id
    WHERE a.parent_id IS NOT NULL
),

-- 向下递归：获取所有后代节点
descendants AS (
    SELECT id, name, parent_id, 'descendant' AS relation, 0 AS level
    FROM departments
    WHERE id = 3  -- 起始节点
    
    UNION ALL
    
    SELECT d.id, d.name, d.parent_id, 'descendant', de.level + 1
    FROM departments d
    INNER JOIN descendants de ON d.parent_id = de.id
)

-- 合并结果（排除重复的起始节点）
SELECT * FROM ancestors
UNION ALL
SELECT * FROM descendants WHERE id != 3
ORDER BY relation, level;
 

查询逻辑说明：

  1. 首先定义 ancestors 递归 CTE，向上查询所有祖先节点（包括起始节点）
  2. 然后定义 descendants 递归 CTE，向下查询所有后代节点（包括起始节点）
  3. 最后用 UNION ALL 合并两个结果集，并用 WHERE id != 3 排除重复的起始节点
  4. relation 字段用于区分是祖先还是后代，level 字段表示与起始节点的层级距离

查询结果包含：

  • 起始节点本身（仅在 ancestors 中保留）
  • 所有上级节点（relation='ancestor'，level 表示向上的层级数）
  • 所有下级节点（relation='descendant'，level 表示向下的层级数）

应用场景：

  • 权限系统中，查询某个角色的所有继承角色（上级）和被继承角色（下级）
  • 商品分类中，查询某个分类的所有上级分类和下级分类
  • 行政区划中，查询某个地区的所有上级地区和下级地区

注意事项：

  • 确保数据中没有循环引用（如 A→B→C→A），否则会导致无限递归
  • 可以通过添加 MAX_RECURSION_DEPTH 限制递归深度，避免查询失控：
    sql
     
     
    SET SESSION cte_max_recursion_depth = 100;  -- 限制最大递归深度为100
     
  • 当层级结构较深时，递归查询可能影响性能，建议根据实际需求优化

 

WITH RECURSIVE 通过path /1/2/3/4 如何获取对应的名称
 
如果你的数据结构中使用类似 /1/2/3/4/ 这样的路径表示层级关系，并且需要根据这个路径获取每个节点的名称，可以使用 WITH RECURSIVE 结合字符串处理函数来解析路径并关联对应的名称。

假设我们有一个表 categories 存储节点信息：

sql
 
 
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    path VARCHAR(255) NOT NULL  -- 例如: /1/、/1/2/、/1/2/3/ 等
);
 

以下是通过路径 /1/2/3/4/ 获取所有节点名称的递归查询：

sql
 
 
WITH RECURSIVE path_nodes AS (
    -- 锚点成员：解析路径的第一个节点
    SELECT 
        -- 提取第一个节点ID
        SUBSTRING_INDEX(SUBSTRING_INDEX('/1/2/3/4/', '/', 2), '/', -1) AS node_id,
        -- 剩余路径
        SUBSTRING('/1/2/3/4/', LENGTH(SUBSTRING_INDEX('/1/2/3/4/', '/', 2)) + 1) AS remaining_path,
        -- 当前层级
        1 AS level
    FROM dual
    WHERE SUBSTRING_INDEX(SUBSTRING_INDEX('/1/2/3/4/', '/', 2), '/', -1) != ''
    
    UNION ALL
    
    -- 递归成员：解析剩余路径中的节点
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(remaining_path, '/', 2), '/', -1) AS node_id,
        SUBSTRING(remaining_path, LENGTH(SUBSTRING_INDEX(remaining_path, '/', 2)) + 1) AS remaining_path,
        level + 1 AS level
    FROM path_nodes
    WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(remaining_path, '/', 2), '/', -1) != ''
)

-- 关联获取节点名称
SELECT 
    pn.level,
    pn.node_id,
    c.name,
    -- 构建完整路径
    (SELECT GROUP_CONCAT(name SEPARATOR ' / ') 
     FROM (
         SELECT c2.name 
         FROM path_nodes pn2
         JOIN categories c2 ON pn2.node_id = c2.id
         WHERE pn2.level <= pn.level
         ORDER BY pn2.level
     ) AS sub) AS full_path_name
FROM path_nodes pn
JOIN categories c ON pn.node_id = c.id
ORDER BY pn.level;
 

查询逻辑说明：

  1. 路径解析部分（path_nodes CTE）：
    • 使用 SUBSTRING_INDEX 函数分割路径字符串
    • 每次提取路径中的一个节点 ID（如从 /1/2/3/4/ 中依次提取 1、2、3、4）
    • 跟踪剩余路径和当前层级
  2. 名称关联部分：
    • 将解析出的节点 ID 与 categories 表关联，获取对应的名称
    • 使用子查询和 GROUP_CONCAT 构建完整的名称路径（如 "根分类 / 子分类 1 / 子分类 2"）

如果你的路径存储格式略有不同（如不带首尾斜杠 1/2/3/4），只需调整字符串处理函数即可：

sql
 
 
-- 处理不带首尾斜杠的路径格式：1/2/3/4
WITH RECURSIVE path_nodes AS (
    SELECT 
        SUBSTRING_INDEX('1/2/3/4', '/', 1) AS node_id,
        SUBSTRING('1/2/3/4', LENGTH(SUBSTRING_INDEX('1/2/3/4', '/', 1)) + 2) AS remaining_path,
        1 AS level
    FROM dual
    WHERE SUBSTRING_INDEX('1/2/3/4', '/', 1) != ''
    
    UNION ALL
    
    SELECT 
        SUBSTRING_INDEX(remaining_path, '/', 1) AS node_id,
        SUBSTRING(remaining_path, LENGTH(SUBSTRING_INDEX(remaining_path, '/', 1)) + 2) AS remaining_path,
        level + 1 AS level
    FROM path_nodes
    WHERE SUBSTRING_INDEX(remaining_path, '/', 1) != ''
)
SELECT pn.level, pn.node_id, c.name
FROM path_nodes pn
JOIN categories c ON pn.node_id = c.id
ORDER BY pn.level;
 

关键函数说明：

  • SUBSTRING_INDEX(str, delim, count)：按分隔符分割字符串并返回指定部分
  • LENGTH(str)：返回字符串长度
  • SUBSTRING(str, pos)：从指定位置开始提取子字符串
  • GROUP_CONCAT()：将多个行的结果连接成一个字符串

这种方法适用于需要将路径字符串解析为具体节点名称的场景，如面包屑导航、分类路径展示等。
 
WITH RECURSIVE 通过path 1/2/3/4 如何获取对应的名称
 
如果路径格式为 1/2/3/4（不带首尾斜杠），可以使用 WITH RECURSIVE 结合字符串函数解析路径，然后关联表获取对应的名称。以下是具体实现：

假设我们有表 categories 存储节点信息：

sql
 
 
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL -- 节点名称
);
 

要解析路径 1/2/3/4 并获取每个节点的名称，可以使用以下查询：

sql
 
 
WITH RECURSIVE path_parser AS (
    -- 锚点成员：处理第一个节点
    SELECT 
        -- 提取第一个ID（1）
        SUBSTRING_INDEX('1/2/3/4', '/', 1) AS node_id,
        -- 剩余路径（2/3/4）
        SUBSTRING(
            '1/2/3/4', 
            LENGTH(SUBSTRING_INDEX('1/2/3/4', '/', 1)) + 2 -- +2是为了跳过已提取的ID和斜杠
        ) AS remaining_path,
        1 AS depth -- 层级深度
    FROM dual
    WHERE SUBSTRING_INDEX('1/2/3/4', '/', 1) != '' -- 确保路径不为空
    
    UNION ALL
    
    -- 递归成员：处理剩余路径
    SELECT 
        -- 从剩余路径提取下一个ID
        SUBSTRING_INDEX(remaining_path, '/', 1) AS node_id,
        -- 更新剩余路径
        SUBSTRING(
            remaining_path, 
            LENGTH(SUBSTRING_INDEX(remaining_path, '/', 1)) + 2
        ) AS remaining_path,
        depth + 1 AS depth
    FROM path_parser
    -- 终止条件：剩余路径不为空
    WHERE SUBSTRING_INDEX(remaining_path, '/', 1) != ''
)

-- 关联表获取名称并生成完整路径
SELECT 
    pp.depth,
    pp.node_id,
    c.name,
    -- 生成类似 "分类1 / 分类2 / 分类3 / 分类4" 的完整路径名称
    (SELECT GROUP_CONCAT(name SEPARATOR ' / ') 
     FROM (
         SELECT c2.name 
         FROM path_parser pp2
         JOIN categories c2 ON pp2.node_id = c2.id
         WHERE pp2.depth <= pp.depth
         ORDER BY pp2.depth
     ) AS subquery
    ) AS full_name_path
FROM path_parser pp
JOIN categories c ON pp.node_id = c.id
ORDER BY pp.depth;
 

查询解析：

  1. 路径解析（path_parser CTE）：
    • 锚点成员：先提取路径中的第一个 ID（1），计算剩余路径（2/3/4）
    • 递归成员：每次从剩余路径中提取下一个 ID（2→3→4），直到路径解析完毕
    • depth 字段记录当前节点在路径中的层级位置
  2. 名称关联与完整路径生成：
    • 通过 node_id 关联 categories 表获取每个节点的名称
    • 使用子查询和 GROUP_CONCAT 函数拼接各级名称，生成类似 "分类 1 / 分类 2 / 分类 3 / 分类 4" 的完整路径

示例结果：

如果表中数据为：

idname
1 电子产品
2 手机
3 智能手机
4 高端机型

查询结果将为：
 

depthnode_idnamefull_name_path
1 1 电子产品 电子产品
2 2 手机 电子产品 / 手机
3 3 智能手机 电子产品 / 手机 / 智能手机
4 4 高端机型 电子产品 / 手机 / 智能手机 / 高端机型

这种方法适用于解析任意长度的层级路径，常用于面包屑导航、分类路径展示等场景。如果需要处理动态路径，可以将硬编码的 '1/2/3/4' 替换为变量或字段名。
 
 
WITH RECURSIVE GROUP_CONCAT
 
WITH RECURSIVE 结合 GROUP_CONCAT 可以实现层级数据的字符串拼接，常用于生成带层级关系的路径字符串（如分类路径、组织路径等）。这种组合特别适合将树形结构数据转换为类似 "父节点 > 子节点 > 孙节点" 的层级文本。

示例场景

假设有一张 categories 表存储树形分类：

idnameparent_id
1 电子产品 NULL
2 手机 1
3 智能手机 2
4 电脑 1
5 笔记本 4

目标

生成每个分类的完整层级路径（如 "电子产品 > 手机 > 智能手机"）。

实现代码

sql
 
 
WITH RECURSIVE category_path AS (
    -- 锚点成员：查询根节点（parent_id 为 NULL）
    SELECT 
        id, 
        name, 
        parent_id, 
        CAST(name AS CHAR(1000)) AS full_path  -- 初始路径为自身名称
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归成员：拼接子节点路径
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        -- 拼接父节点路径 + 当前节点名称
        CONCAT(cp.full_path, ' > ', c.name) AS full_path
    FROM categories c
    INNER JOIN category_path cp 
        ON c.parent_id = cp.id  -- 关联父节点
)

-- 最终查询：获取所有节点的完整路径
SELECT 
    id, 
    name, 
    full_path 
FROM category_path
ORDER BY id;
 

查询结果

idnamefull_path
1 电子产品 电子产品
2 手机 电子产品 > 手机
3 智能手机 电子产品 > 手机 > 智能手机
4 电脑 电子产品 > 电脑
5 笔记本 电子产品 > 电脑 > 笔记本

代码解析

  1. 锚点成员：
    • 先查询所有根节点（parent_id IS NULL）；
    • 用 CAST(name AS CHAR(1000)) 初始化路径为节点自身名称（指定长度避免截断）。
  2. 递归成员：
    • 关联子节点（c.parent_id = cp.id）；
    • 用 CONCAT(cp.full_path, ' > ', c.name) 拼接父节点路径和当前节点名称，形成完整层级路径。
  3. GROUP_CONCAT 扩展用法：
    如果需要聚合同一层级的所有子节点（如 "电子产品包含：手机，电脑"），可以在递归后结合 GROUP_CONCAT：
    sql
     
     
    WITH RECURSIVE category_children AS (
    -- 锚点：根节点
    SELECT 
        id, 
        parent_id, 
        name 
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归：子节点
    SELECT 
        c.id, 
        c.parent_id, 
        c.name 
    FROM categories c
    INNER JOIN category_children cc 
        ON c.parent_id = cc.id
)

-- 聚合每个父节点的所有子节点
SELECT 
    parent_id,
    GROUP_CONCAT(name SEPARATOR ', ') AS children
FROM category_children
WHERE parent_id IS NOT NULL  -- 排除根节点自身
GROUP BY parent_id;
     

    结果：
    parent_idchildren
    1 手机，电脑
    2 智能手机
    4 笔记本

关键知识点

  • 递归拼接逻辑：核心是通过 CONCAT 在每次递归中累加父节点路径，形成层级字符串。
  • GROUP_CONCAT 作用：既可以在递归内部用于累加路径，也可以在递归后用于聚合同一层级的节点。
  • 长度限制：GROUP_CONCAT 有默认长度限制（1024 字符），可通过 SET group_concat_max_len = 1000000; 调整。
  • 性能注意：层级过深或数据量大时，递归 + 字符串拼接可能影响性能，建议按需限制层级深度。

这种组合非常适合生成树形结构的文本路径，常见于面包屑导航、分类路径展示等场景。
 
 WITH RECURSIVE 显示全路径和路径名称
 

WITH RECURSIVE
-- 步骤1：解析原始ID路径，获取每个节点ID和位置
path_nodes AS (
SELECT
'1/2/3/4' AS original_path, -- 原始ID路径
SUBSTRING_INDEX('1/2/3/4', '/', 1) AS node_id, -- 提取第一个ID
SUBSTRING('1/2/3/4', LENGTH(SUBSTRING_INDEX('1/2/3/4', '/', 1)) + 2) AS remaining_path, -- 剩余路径
1 AS depth -- 层级深度
FROM dual
WHERE SUBSTRING_INDEX('1/2/3/4', '/', 1) != '' -- 确保路径不为空

UNION ALL

SELECT
original_path,
SUBSTRING_INDEX(remaining_path, '/', 1) AS node_id, -- 提取下一个ID
SUBSTRING(remaining_path, LENGTH(SUBSTRING_INDEX(remaining_path, '/', 1)) + 2) AS remaining_path, -- 更新剩余路径
depth + 1 AS depth -- 层级+1
FROM path_nodes
WHERE SUBSTRING_INDEX(remaining_path, '/', 1) != '' -- 剩余路径不为空时继续递归
),

-- 步骤2：关联名称并生成累积的ID路径和名称路径
full_paths AS (
-- 锚点：第一个节点
SELECT
pn.node_id,
pn.depth,
pn.node_id AS id_path, -- 初始ID路径（仅第一个节点）
c.name AS name_path -- 初始名称路径（仅第一个节点）
FROM path_nodes pn
JOIN categories c ON pn.node_id = c.id
WHERE pn.depth = 1

UNION ALL

-- 递归：拼接后续节点
SELECT
pn.node_id,
pn.depth,
CONCAT(fp.id_path, '/', pn.node_id) AS id_path, -- 拼接ID路径
CONCAT(fp.name_path, ' / ', c.name) AS name_path -- 拼接名称路径
FROM path_nodes pn
JOIN full_paths fp ON pn.depth = fp.depth + 1 -- 按层级关联
JOIN categories c ON pn.node_id = c.id
)

-- 最终结果：获取完整的ID路径和名称路径
SELECT
original_path AS full_id_path, -- 原始完整ID路径
name_path AS full_name_path -- 对应的完整名称路径
FROM full_paths, path_nodes
WHERE full_paths.depth = (SELECT MAX(depth) FROM path_nodes) -- 取最深层级的完整路径
GROUP BY original_path, name_path; -- 去重（因关联导致的重复行）

 

WITH RECURSIVE 显示每个层级的id全路径和路径名称
 
已完成思考
 
 
要使用 WITH RECURSIVE 显示树形结构中每个层级的 ID 全路径和路径名称，需基于递归 CTE（公用表表达式）实现。以下是具体实现方案：

假设场景

假设有一个树形结构表（如部门、分类等），表名为 tree_nodes，结构如下：

sql
 
 
CREATE TABLE tree_nodes (
  id INT PRIMARY KEY,         -- 节点ID
  parent_id INT,              -- 父节点ID（顶级节点为NULL）
  name VARCHAR(100),          -- 节点名称
  FOREIGN KEY (parent_id) REFERENCES tree_nodes(id)
);
 

插入测试数据：

sql
 
 
INSERT INTO tree_nodes VALUES 
(1, NULL, '顶级节点'),
(2, 1, '二级节点A'),
(3, 1, '二级节点B'),
(4, 2, '三级节点A1'),
(5, 4, '四级节点A1-1'),
(6, 3, '三级节点B1');
 

实现代码

使用递归 CTE 生成每个节点的全路径（ID 路径和名称路径）：

sql
 
 
WITH RECURSIVE node_paths AS (
  -- 1. 锚点查询：获取顶级节点（parent_id为NULL）
  SELECT 
    id,
    parent_id,
    name,
    -- 顶级节点的ID路径就是自身ID，名称路径就是自身名称
    CAST(id AS CHAR(255)) AS full_id_path,  -- 转换为字符串方便拼接
    name AS full_name_path
  FROM tree_nodes
  WHERE parent_id IS NULL

  UNION ALL

  -- 2. 递归查询：关联子节点与父节点，拼接路径
  SELECT 
    c.id,
    c.parent_id,
    c.name,
    -- 父节点的ID路径 + 当前节点ID（用分隔符拼接）
    CONCAT(p.full_id_path, '/', c.id) AS full_id_path,
    -- 父节点的名称路径 + 当前节点名称（用分隔符拼接）
    CONCAT(p.full_name_path, ' -> ', c.name) AS full_name_path
  FROM tree_nodes c
  -- 关联递归结果（父节点）
  INNER JOIN node_paths p ON c.parent_id = p.id
)

-- 3. 查询最终结果
SELECT 
  id, 
  parent_id, 
  name, 
  full_id_path AS "ID全路径", 
  full_name_path AS "名称全路径"
FROM node_paths
ORDER BY full_id_path;
 

结果说明

上述代码执行后，会输出每个节点的完整路径信息：

idparent_idnameID 全路径名称全路径
1 NULL 顶级节点 1 顶级节点
2 1 二级节点 A 1/2 顶级节点 -> 二级节点 A
4 2 三级节点 A1 1/2/4 顶级节点 -> 二级节点 A -> 三级节点 A1
5 4 四级节点 A1-1 1/2/4/5 顶级节点 -> 二级节点 A -> 三级节点 A1 -> 四级节点 A1-1
3 1 二级节点 B 1/3 顶级节点 -> 二级节点 B
6 3 三级节点 B1 1/3/6 顶级节点 -> 二级节点 B -> 三级节点 B1

关键逻辑解析

  1. 锚点查询：筛选出顶级节点（parent_id IS NULL），初始化路径为自身信息。
  2. 递归查询：通过 INNER JOIN 关联子节点（tree_nodes c）和父节点（递归结果 node_paths p），利用 CONCAT 函数拼接父路径与当前节点信息，形成完整路径。
  3. 分隔符：示例中用 / 分隔 ID，用 -> 分隔名称，可根据需求修改（如 ,、| 等）。

根据实际表结构，只需调整表名（tree_nodes）、字段名（id、parent_id、name）即可复用此逻辑。

 

 

-----------------------------
参考：
 
WITH RECURSIVE node_paths AS (
  -- 1. 锚点查询：获取顶级节点（parent_id为NULL）
  SELECT 
    id,
    parent_id,
    name,
    -- 顶级节点的ID路径就是自身ID，名称路径就是自身名称
    CAST(id AS CHAR(255)) AS full_id_path,  -- 转换为字符串方便拼接
    name AS full_name_path
  FROM tree_nodes
  WHERE parent_id IS NULL

  UNION ALL

  -- 2. 递归查询：关联子节点与父节点，拼接路径
  SELECT 
    c.id,
    c.parent_id,
    c.name,
    -- 父节点的ID路径 + 当前节点ID（用分隔符拼接）
    CONCAT(p.full_id_path, '/', c.id) AS full_id_path,
    -- 父节点的名称路径 + 当前节点名称（用分隔符拼接）
    CONCAT(p.full_name_path, ' -> ', c.name) AS full_name_path
  FROM tree_nodes c
  -- 关联递归结果（父节点）
  INNER JOIN node_paths p ON c.parent_id = p.id
)

-- 3. 查询最终结果
SELECT 
  id, 
  parent_id, 
  name, 
  full_id_path AS "ID全路径", 
  full_name_path AS "名称全路径"
FROM node_paths
ORDER BY full_id_path;
参考：
WITH RECURSIVE category_path AS (
    -- 锚点成员：查询根节点（parent_id 为 NULL）
    SELECT 
        id, 
        name, 
        parent_id, 
        CAST(name AS CHAR(1000)) AS full_path  -- 初始路径为自身名称
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归成员：拼接子节点路径
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        -- 拼接父节点路径 + 当前节点名称
        CONCAT(cp.full_path, ' > ', c.name) AS full_path
    FROM categories c
    INNER JOIN category_path cp 
        ON c.parent_id = cp.id  -- 关联父节点
)

-- 最终查询：获取所有节点的完整路径
SELECT 
    id, 
    name, 
    full_path 
FROM category_path
ORDER BY id;
 
 
 
 
posted @ 2025-08-26 23:21  hanease  阅读(41)  评论(0)    收藏  举报