顺子の杂货铺
生命不息，折腾不止，且行且珍惜~
        找回密码
当前位置：順子の杂货铺 > BigData > 正文

18-递归查询与树形结构

2026-01-18 分类：BigData / BLOG / Database / SQL 阅读(2) 评论(0)
DMIT VPS

递归查询与树形结构

递归查询是处理树形结构、层级数据的重要技术，本篇介绍各数据库的实现方式。

📊 支持情况

数据库CTE递归CONNECT BYSTART WITH备注
MySQL✅ 8.0+❌❌使用WITH RECURSIVE
Oracle✅✅✅支持两种方式
ClickHouse✅––使用WITH RECURSIVE
Hologres✅––兼容PostgreSQL
MaxCompute✅––使用WITH RECURSIVE
Hive❌✅✅使用CONNECT BY

1. 基础递归CTE

MySQL / ClickHouse / Hologres / MaxCompute

-- 基础递归CTE语法
WITH RECURSIVE cte AS (
    -- 锚点部分：初始查询
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL  -- 从根节点开始

    UNION ALL

    -- 递归部分：关联查询
    SELECT c.id, c.name, c.parent_id, cte.level + 1
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

Oracle 使用WITH RECURSIVE

WITH RECURSIVE cte (id, name, parent_id, level) AS (
    SELECT id, name, parent_id, 0
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, cte.level + 1
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

Oracle 使用CONNECT BY

-- 传统方式（11g及之前）
SELECT
    id,
    name,
    parent_id,
    LEVEL AS level
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

2. 树形路径查询

MySQL 路径拼接

WITH RECURSIVE cte AS (
    SELECT
        id,
        name,
        parent_id,
        CAST(name AS CHAR(200)) AS path,
        0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.name,
        c.parent_id,
        CONCAT(cte.path, ' > ', c.name),
        cte.level + 1
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT id, name, parent_id, path, level
FROM cte
ORDER BY path;

Oracle 路径函数

-- 使用SYS_CONNECT_BY_PATH
SELECT
    id,
    name,
    SYS_CONNECT_BY_PATH(name, ' > ') AS path,
    LEVEL AS level
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

Oracle PRIOR 关键字

-- 获取父节点信息
SELECT
    id,
    name,
    PRIOR name AS parent_name,
    LEVEL
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

3. 上下级关系查询

向下查询（获取所有子节点）

-- MySQL - 查询某节点的所有后代
WITH RECURSIVE cte AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1  -- 从指定节点开始

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

向上查询（获取所有祖先）

-- MySQL - 查询某节点的所有祖先
WITH RECURSIVE cte AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 5  -- 指定节点

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN cte ON c.id = cte.parent_id
)
SELECT * FROM cte;

Oracle 向上查询

-- 使用CONNECT BY PRIOR
SELECT
    id,
    name,
    LEVEL
FROM categories
START WITH id = 5
CONNECT BY PRIOR parent_id = id;

4. 层级统计

每层节点统计

-- MySQL - 统计每个层级的节点数量
WITH RECURSIVE cte AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, cte.level + 1
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT level, COUNT(*) AS node_count
FROM cte
GROUP BY level
ORDER BY level;

累计子节点数

-- MySQL - 每个节点的子节点总数
WITH RECURSIVE cte AS (
    SELECT id, name, parent_id, 1 AS subtree_size
    FROM categories

    UNION ALL

    SELECT c.id, c.name, c.parent_id, cte.subtree_size + 1
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
    WHERE c.parent_id IS NOT NULL
)
SELECT id, name, COUNT(*) AS direct_children
FROM categories
GROUP BY id, name;

5. 组织架构查询

员工上下级关系

-- MySQL
WITH RECURSIVE employee_hierarchy AS (
    SELECT
        emp_id,
        name,
        manager_id,
        0 AS level,
        CAST(name AS CHAR(200)) AS path
    FROM employees
    WHERE manager_id IS NULL  -- CEO

    UNION ALL

    SELECT
        e.emp_id,
        e.name,
        e.manager_id,
        eh.level + 1,
        CONCAT(eh.path, ' > ', e.name)
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.emp_id
)
SELECT * FROM employee_hierarchy;

部门层级

-- Oracle
SELECT
    d.dept_id,
    d.dept_name,
    d.parent_dept_id,
    LEVEL,
    SYS_CONNECT_BY_PATH(d.dept_name, '/') AS path
FROM departments d
START WITH d.parent_dept_id IS NULL
CONNECT BY PRIOR d.dept_id = d.parent_dept_id;

6. 实战案例

场景1：评论无限回复

-- 表结构：评论表（parent_comment_id为NULL表示顶级评论）
WITH RECURSIVE comment_tree AS (
    SELECT
        comment_id,
        content,
        user_id,
        parent_comment_id,
        0 AS level,
        CAST(comment_id AS CHAR(50)) AS path
    FROM comments
    WHERE parent_comment_id IS NULL

    UNION ALL

    SELECT
        c.comment_id,
        c.content,
        c.user_id,
        c.parent_comment_id,
        ct.level + 1,
        CONCAT(ct.path, '-', c.comment_id)
    FROM comments c
    INNER JOIN comment_tree ct ON c.parent_comment_id = ct.comment_id
)
SELECT
    comment_id,
    content,
    user_id,
    level,
    REPEAT('  ', level) || content AS indented_content
FROM comment_tree
ORDER BY path;

场景2：区域级联查询

-- 查询某省下所有城市
WITH RECURSIVE region_tree AS (
    SELECT region_id, region_name, parent_id, 0 AS level
    FROM regions
    WHERE region_id = 1  -- 省份ID

    UNION ALL

    SELECT r.region_id, r.region_name, r.parent_id, rt.level + 1
    FROM regions r
    INNER JOIN region_tree rt ON r.parent_id = rt.region_id
)
SELECT * FROM region_tree;

场景3：菜单树

-- MySQL - 生成菜单树结构
WITH RECURSIVE menu_tree AS (
    SELECT
        menu_id,
        menu_name,
        parent_id,
        CAST(menu_name AS CHAR(500)) AS full_path,
        0 AS level
    FROM menus
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        m.menu_id,
        m.menu_name,
        m.parent_id,
        CONCAT(mt.full_path, ' > ', m.menu_name),
        mt.level + 1
    FROM menus m
    INNER JOIN menu_tree mt ON m.parent_id = mt.menu_id
)
SELECT
    menu_id,
    menu_name,
    parent_id,
    level,
    REPEAT('  ', level) || menu_name AS display_name,
    full_path
FROM menu_tree
ORDER BY full_path;

场景4：Bom物料清单

-- 查询产品的完整物料清单
WITH RECURSIVE bom AS (
    SELECT
        part_id,
        part_name,
        parent_part_id,
        quantity,
        1 AS level,
        CAST(part_name AS CHAR(500)) AS path
    FROM bom
    WHERE parent_part_id IS NULL  -- 成品

    UNION ALL

    SELECT
        b.part_id,
        b.part_name,
        b.parent_part_id,
        b.quantity,
        bom.level + 1,
        CONCAT(bom.path, ' > ', b.part_name)
    FROM bom b
    INNER JOIN bom ON b.parent_part_id = bom.part_id
)
SELECT * FROM bom;

7. 循环检测与控制

最大递归深度

-- MySQL - 限制递归深度
WITH RECURSIVE cte AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, cte.level + 1
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
    WHERE cte.level < 10  -- 限制最大10层
)
SELECT * FROM cte;

检测循环

-- Oracle - NOCYCLE
SELECT
    id,
    name,
    LEVEL
FROM categories
START WITH parent_id IS NULL
CONNECT BY NOCYCLE PRIOR id = parent_id;

⚠️ 注意事项

1. 性能问题

-- ✅ 建议：在锚点部分添加数据过滤
WITH RECURSIVE cte AS (
    SELECT * FROM categories WHERE id IN (1, 2, 3)  -- 先过滤
    UNION ALL
    ...
)

-- ❌ 避免：全表扫描后递归
WITH RECURSIVE cte AS (
    SELECT * FROM categories  -- 全表扫描
    UNION ALL
    ...
)

2. NULL处理

-- parent_id为NULL表示根节点
-- 递归时要注意NULL的关联条件
SELECT * FROM categories c
INNER JOIN cte ON c.parent_id = cte.id;
-- 当c.parent_id为NULL时，JOIN结果为NULL，该行被跳过

3. 循环数据

-- 检测循环：A->B->A
-- MySQL无法自动检测，需要在WHERE中限制
WHERE NOT EXISTS (
    SELECT 1 FROM cte c2 WHERE c2.id = c.id
)

📝 练习题

-- 建表
CREATE TABLE org_structure (
    emp_id INT,
    name STRING,
    position STRING,
    manager_id INT
);

INSERT INTO org_structure VALUES
(1, '张CEO', 'CEO', NULL),
(2, '李总监', '销售总监', 1),
(3, '王总监', '技术总监', 1),
(4, '赵经理', '销售经理', 2),
(5, '钱经理', '技术经理', 3),
(6, '孙员工', '销售代表', 4),
(7, '周员工', '销售代表', 4),
(8, '吴员工', '开发工程师', 5);

要求：

  1. 生成完整的组织架构树
  2. 查询张CEO的所有下属（直接或间接）
  3. 查询孙员工的汇报链路
  4. 统计每个管理层级的下属人数
-- 参考答案（MySQL）
-- 1. 完整组织架构
WITH RECURSIVE org AS (
    SELECT emp_id, name, position, manager_id, 0 AS level, CAST(name AS CHAR(200)) AS path
    FROM org_structure
    WHERE manager_id IS NULL

    UNION ALL

    SELECT o.emp_id, o.name, o.position, o.manager_id, org.level + 1, CONCAT(org.path, ' > ', o.name)
    FROM org_structure o
    INNER JOIN org ON o.manager_id = org.emp_id
)
SELECT * FROM org ORDER BY path;

-- 2. 张CEO的所有下属
WITH RECURSIVE subs AS (
    SELECT * FROM org_structure WHERE manager_id = 1
    UNION ALL
    SELECT o.* FROM org_structure o
    INNER JOIN subs ON o.manager_id = subs.emp_id
)
SELECT * FROM subs;

-- 3. 孙员工的汇报链路
WITH RECURSIVE path AS (
    SELECT * FROM org_structure WHERE emp_id = 6
    UNION ALL
    SELECT o.* FROM org_structure o
    INNER JOIN path ON o.emp_id = path.manager_id
)
SELECT * FROM path ORDER BY manager_id;

-- 4. 每层下属人数
WITH RECURSIVE org AS (
    SELECT emp_id, name, manager_id, 0 AS level FROM org_structure WHERE manager_id IS NULL
    UNION ALL
    SELECT o.emp_id, o.name, o.manager_id, org.level + 1 FROM org_structure o
    INNER JOIN org ON o.manager_id = org.emp_id
)
SELECT level, COUNT(*) AS employee_count FROM org GROUP BY level;
赞(0)
未经允许不得转载：順子の杂货铺 » 18-递归查询与树形结构
标签：
Avatar photo

shunzi

一半忧伤，一半明媚！喜欢看书，听歌，写字，打游戏，交朋友！
搬瓦工VPS

相关推荐

评论 抢沙发

Avatar photo

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
吐血推荐

New

热门文章

热门标签

sql (48)mysql (22)oracle (21)python (15)Hive (13)MaxCompute (13)Hologres (13)ClickHouse (12)rm (6)password (5)port (5)install (4)linux (4)log (4)join (3)pos (3)google (3)vi (3)tab (3)redis (3)deepin (3)htaccess (2)master (2)centos (2)kangle (2)easypanel (2)list (2)django (2)plsql (2)docker (2)

网站统计

  • 日志总数：88
  • 评论总数：55
  • 标签总数：125
  • 最后更新：2026-01-18

分享创造快乐

联系我们联系我们