课程 6.6: 将递归CTE应用于现实世界问题

在上一课中，我们探讨了常规（非递归）CTE——一种组织和结构化SQL查询的工具。现在我们转向它们最强大的变体：递归CTE。

递归CTE使您能够处理层次结构、树状和网络数据结构。它们解决了许多传统上需要过程代码或复杂存储过程的现实世界问题。

递归CTE

什么是递归CTE？

递归CTE是一个引用自身的CTE，允许您逐层遍历层次结构。

递归CTE的结构：

WITH RECURSIVE cte_name AS (
    -- 锚定成员（基本情况）
    SELECT ... 
    WHERE anchor_condition
    
    UNION ALL
    
    -- 递归成员（如何移动到下一层）
    SELECT ...
    FROM cte_name
    WHERE stop_condition
)
SELECT * FROM cte_name;

关键组件：

锚定成员 — 递归的起始点（通常是根记录）
UNION ALL — 组合锚定和递归的结果
递归成员 — 如何从一个层次过渡到另一个层次
停止条件 — 何时终止递归

示例 1：类别层次结构

最常见的应用之一是处理电子商务中的产品类别层次结构。

表结构：

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(100),
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);

INSERT INTO categories VALUES
(1, NULL, '电子产品'),
(2, 1, '计算机'),
(3, 1, '手机'),
(4, 2, '笔记本电脑'),
(5, 2, '台式电脑'),
(6, 4, '戴尔笔记本电脑'),
(7, 4, '惠普笔记本电脑'),
(8, 3, '智能手机'),
(9, 3, '平板电脑');

任务：获取从根到叶子的完整类别层次结构

WITH RECURSIVE category_hierarchy AS (
    -- 锚定成员：从根类别开始
    SELECT
        category_id,
        parent_id,
        name,
        1 AS level,
        name AS full_path
    FROM
        categories
    WHERE
        parent_id IS NULL
    
    UNION ALL
    
    -- 递归成员：添加子类别
    SELECT
        c.category_id,
        c.parent_id,
        c.name,
        ch.level + 1,
        CONCAT(ch.full_path, ' → ', c.name)
    FROM
        categories c
    JOIN
        category_hierarchy ch ON c.parent_id = ch.category_id
)
SELECT
    category_id,
    REPEAT('  ', level - 1) AS indent,
    name,
    level,
    full_path
FROM
    category_hierarchy
ORDER BY
    level,
    category_id;

结果：

category_id | indent | name              | level | full_path
1           |        | 电子产品         | 1     | 电子产品
2           |   | 计算机          | 2     | 电子产品 → 计算机
4           |       | 笔记本电脑       | 3     | 电子产品 → 计算机 → 笔记本电脑
6           |           | 戴尔笔记本电脑  | 4     | 电子产品 → 计算机 → 笔记本电脑 → 戴尔笔记本电脑
7           |           | 惠普笔记本电脑  | 4     | 电子产品 → 计算机 → 笔记本电脑 → 惠普笔记本电脑
5           |       | 台式电脑         | 3     | 电子产品 → 计算机 → 台式电脑
3           |   | 手机            | 2     | 电子产品 → 手机
8           |       | 智能手机         | 3     | 电子产品 → 手机 → 智能手机
9           |       | 平板电脑         | 3     | 电子产品 → 手机 → 平板电脑

发生了什么：

锚定成员仅找到电子产品（parent_id IS NULL）
递归成员找到计算机和手机（电子产品的子类）
该过程重复，直到找到所有叶子

示例 2：组织结构图

通常需要显示公司结构及管理链。

表结构：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    manager_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES
(1, '约翰·史密斯', '首席执行官', NULL, 150000),
(2, '安娜·约翰逊', '销售总监', 1, 100000),
(3, '彼得·威廉姆斯', 'IT总监', 1, 120000),
(4, '玛丽亚·布朗', '销售经理', 2, 60000),
(5, '亚历克斯·戴维斯', '销售经理', 2, 60000),
(6, '谢尔盖·米勒', '高级开发人员', 3, 90000),
(7, '奥尔加·威尔逊', '开发人员', 6, 70000),
(8, '德米特里·摩尔', '开发人员', 6, 70000);

任务：显示组织结构及管理链

WITH RECURSIVE org_chart AS (
    -- 锚定：首席执行官
    SELECT
        employee_id,
        name,
        position,
        manager_id,
        salary,
        1 AS level,
        name AS management_chain
    FROM
        employees
    WHERE
        manager_id IS NULL
    
    UNION ALL
    
    -- 递归：添加下属
    SELECT
        e.employee_id,
        e.name,
        e.position,
        e.manager_id,
        e.salary,
        oc.level + 1,
        CONCAT(oc.management_chain, ' → ', e.name)
    FROM
        employees e
    JOIN
        org_chart oc ON e.manager_id = oc.employee_id
    WHERE
        oc.level < 10  -- 防止无限递归
)
SELECT
    employee_id,
    REPEAT('│ ', level - 1) AS hierarchy,
    name,
    position,
    salary,
    management_chain
FROM
    org_chart
ORDER BY
    level,
    employee_id;

结果：

employee_id | hierarchy | name         | position            | salary | management_chain
1           |          | 约翰·史密斯   | 首席执行官          | 150000 | 约翰·史密斯
2           | │        | 安娜·约翰逊 | 销售总监          | 100000 | 约翰·史密斯 → 安娜·约翰逊
4           | │ │      | 玛丽亚·布朗  | 销售经理          | 60000  | 约翰·史密斯 → 安娜·约翰逊 → 玛丽亚·布朗
5           | │ │      | 亚历克斯·戴维斯 | 销售经理          | 60000  | 约翰·史密斯 → 安娜·约翰逊 → 亚历克斯·戴维斯
3           | │        | 彼得·威廉姆斯| IT总监           | 120000 | 约翰·史密斯 → 彼得·威廉姆斯
6           | │ │      | 谢尔盖·米勒 | 高级开发人员      | 90000  | 约翰·史密斯 → 彼得·威廉姆斯 → 谢尔盖·米勒
7           | │ │ │    | 奥尔加·威尔逊 | 开发人员          | 70000  | 约翰·史密斯 → 彼得·威廉姆斯 → 谢尔盖·米勒 → 奥尔加·威尔逊
8           | │ │ │    | 德米特里·摩尔 | 开发人员          | 70000  | 约翰·史密斯 → 彼得·威廉姆斯 → 谢尔盖·米勒 → 德米特里·摩尔

应用：计算部门预算

WITH RECURSIVE org_chart AS (
    SELECT
        employee_id,
        name,
        position,
        salary,
        1 AS level
    FROM
        employees
    WHERE
        manager_id IS NULL
    
    UNION ALL
    
    SELECT
        e.employee_id,
        e.name,
        e.position,
        e.salary,
        oc.level + 1
    FROM
        employees e
    JOIN
        org_chart oc ON e.manager_id = oc.employee_id
)
SELECT
    name AS position,
    COUNT(*) AS number_of_employees,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS average_salary
FROM
    org_chart
GROUP BY
    employee_id,
    name
ORDER BY
    SUM(salary) DESC;

示例 3：物料清单（BOM）

在制造业中，您需要知道哪些组件构成一个产品。

表结构：

CREATE TABLE bom (
    product_id INT,
    component_id INT,
    quantity INT,
    PRIMARY KEY (product_id, component_id)
);

INSERT INTO bom VALUES
(1, 2, 1),      -- 笔记本电脑由1块主板组成
(1, 3, 2),      -- 和2根内存条
(1, 4, 1),      -- 和1个硬盘
(2, 5, 1),      -- 主板由1个芯片组组成
(2, 6, 20),     -- 和20个电阻
(4, 7, 1);      -- 硬盘由1个主轴组成

任务：扩展BOM到完整组件列表

WITH RECURSIVE bom_expanded AS (
    -- 锚定：成品（不作为组件使用）
    SELECT
        product_id,
        product_id AS component_id,
        1 AS quantity,
        0 AS level,
        CAST(product_id AS CHAR(100)) AS path
    FROM
        (SELECT DISTINCT product_id FROM bom
         UNION
         SELECT DISTINCT component_id FROM bom) AS products
    
    UNION ALL
    
    -- 递归：扩展每个组件
    SELECT
        be.product_id,
        b.component_id,
        be.quantity * b.quantity,
        be.level + 1,
        CONCAT(be.path, ' → ', b.component_id)
    FROM
        bom_expanded be
    JOIN
        bom b ON be.component_id = b.product_id
    WHERE
        be.level < 10
)
SELECT
    product_id,
    component_id,
    quantity,
    level,
    path
FROM
    bom_expanded
WHERE
    level > 0
ORDER BY
    product_id,
    level,
    component_id;

示例 4：菜单结构（菜单树）

电子商务网站和Web应用程序通常具有多级菜单。

表结构：

CREATE TABLE menu (
    menu_id INT PRIMARY KEY,
    parent_menu_id INT,
    title VARCHAR(100),
    url VARCHAR(255),
    sort_order INT,
    FOREIGN KEY (parent_menu_id) REFERENCES menu(menu_id)
);

INSERT INTO menu VALUES
(1, NULL, '首页', '/', 1),
(2, NULL, '目录', '/catalog', 2),
(3, NULL, '关于我们', '/about', 3),
(4, 2, '计算机', '/catalog/computers', 1),
(5, 2, '配件', '/catalog/accessories', 2),
(6, 4, '笔记本电脑', '/catalog/computers/laptops', 1),
(7, 4, '台式电脑', '/catalog/computers/desktops', 2),
(8, 5, '鼠标', '/catalog/accessories/mice', 1),
(9, 5, '键盘', '/catalog/accessories/keyboards', 2),
(10, 3, '历史', '/about/history', 1),
(11, 3, '团队', '/about/team', 2);

任务：以树形结构显示菜单并缩进

WITH RECURSIVE menu_tree AS (
    -- 锚定：主菜单项
    SELECT
        menu_id,
        parent_menu_id,
        title,
        url,
        1 AS level,
        title AS breadcrumb
    FROM
        menu
    WHERE
        parent_menu_id IS NULL
    ORDER BY
        sort_order
    
    UNION ALL
    
    -- 递归：子菜单
    SELECT
        m.menu_id,
        m.parent_menu_id,
        m.title,
        m.url,
        mt.level + 1,
        CONCAT(mt.breadcrumb, ' > ', m.title)
    FROM
        menu m
    JOIN
        menu_tree mt ON m.parent_menu_id = mt.menu_id
)
SELECT
    menu_id,
    REPEAT('  ', level - 1) AS indent,
    title,
    url,
    breadcrumb
FROM
    menu_tree
ORDER BY
    level,
    menu_id;

结果：

menu_id | indent | title            | url                      | breadcrumb
1       |        | 首页             | /                        | 首页
2       |        | 目录             | /catalog                 | 目录
4       |   | 计算机         | /catalog/computers       | 目录 > 计算机
6       |       | 笔记本电脑     | /catalog/computers/laptops | 目录 > 计算机 > 笔记本电脑
7       |       | 台式电脑       | /catalog/computers/desktops | 目录 > 计算机 > 台式电脑
5       |   | 配件           | /catalog/accessories     | 目录 > 配件
8       |       | 鼠标           | /catalog/accessories/mice | 目录 > 配件 > 鼠标
9       |       | 键盘           | /catalog/accessories/keyboards | 目录 > 配件 > 键盘
3       |        | 关于我们         | /about                   | 关于我们
10      |   | 历史           | /about/history           | 关于我们 > 历史
11      |   | 团队           | /about/team              | 关于我们 > 团队

示例 5：图中的路径查找

递归CTE用于查找图中两个节点之间的所有路径（例如，物流系统中的交付路线）。

表结构：

CREATE TABLE cities (
    city_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE routes (
    from_city_id INT,
    to_city_id INT,
    distance INT,
    PRIMARY KEY (from_city_id, to_city_id),
    FOREIGN KEY (from_city_id) REFERENCES cities(city_id),
    FOREIGN KEY (to_city_id) REFERENCES cities(city_id)
);

INSERT INTO cities VALUES (1, '莫斯科'), (2, '特维尔'), (3, '斯摩棱斯克'), (4, '布良斯克');

INSERT INTO routes VALUES
(1, 2, 170),   -- 莫斯科 → 特维尔
(2, 3, 220),   -- 特维尔 → 斯摩棱斯克
(1, 4, 380),   -- 莫斯科 → 布良斯克
(4, 3, 250);   -- 布良斯克 → 斯摩棱斯克

任务：查找从莫斯科到斯摩棱斯克的所有路线

WITH RECURSIVE routes_search AS (
    -- 锚定：从莫斯科开始（city_id = 1）
    SELECT
        from_city_id,
        to_city_id,
        distance,
        1 AS hops,
        CAST(to_city_id AS CHAR(1000)) AS path,
        distance AS total_distance
    FROM
        routes
    WHERE
        from_city_id = 1
    
    UNION ALL
    
    -- 递归：从每个目的地继续
    SELECT
        rs.from_city_id,
        r.to_city_id,
        r.distance,
        rs.hops + 1,
        CONCAT(rs.path, ',', r.to_city_id),
        rs.total_distance + r.distance
    FROM
        routes_search rs
    JOIN
        routes r ON rs.to_city_id = r.from_city_id
    WHERE
        rs.hops < 10  -- 防止循环
        AND rs.path NOT LIKE CONCAT('%,', r.to_city_id, '%')  -- 避免循环
)
SELECT
    from_city_id,
    to_city_id,
    hops,
    path,
    total_distance,
    ROUND(total_distance / hops, 2) AS average_distance_between_cities
FROM
    routes_search
WHERE
    to_city_id = 3  -- 目的地：斯摩棱斯克
ORDER BY
    hops,
    total_distance;

最佳实践与优化

1. 始终定义停止条件

没有停止条件的递归将导致无限循环：

-- ❌ 不好：可能导致无限递归
WITH RECURSIVE bad_recursion AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM bad_recursion
)
SELECT * FROM bad_recursion;

-- ✅ 好：包含停止条件
WITH RECURSIVE good_recursion AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM good_recursion
    WHERE n < 1000
)
SELECT * FROM good_recursion;

2. 使用UNION ALL而不是UNION

UNION ALL不会删除重复项并且运行更快：

-- ❌ 较慢
WITH RECURSIVE cte AS (
    SELECT ...
    UNION  -- 删除重复项
    SELECT ...
)

-- ✅ 更快
WITH RECURSIVE cte AS (
    SELECT ...
    UNION ALL  -- 不删除重复项
    SELECT ...
)

3. 避免循环引用

使用路径检查来防止循环：

WITH RECURSIVE safe_recursion AS (
    SELECT
        id,
        parent_id,
        CAST(id AS CHAR(1000)) AS path
    FROM
        table_name
    WHERE
        parent_id IS NULL
    
    UNION ALL
    
    SELECT
        t.id,
        t.parent_id,
        CONCAT(sr.path, ',', t.id)
    FROM
        table_name t
    JOIN
        safe_recursion sr ON t.parent_id = sr.id
    WHERE
        sr.path NOT LIKE CONCAT('%,', t.id, '%')  -- 循环检查
        AND sr.path NOT LIKE CONCAT(t.id, ',%')
)
SELECT * FROM safe_recursion;

4. 限制递归深度

明确限制最大递归深度：

WITH RECURSIVE limited_recursion AS (
    SELECT
        id,
        parent_id,
        0 AS level
    FROM table_name
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT
        t.id,
        t.parent_id,
        lr.level + 1
    FROM
        table_name t
    JOIN
        limited_recursion lr ON t.parent_id = lr.id
    WHERE
        lr.level < 20  -- 最大20层
)
SELECT * FROM limited_recursion;

递归CTE的应用矩阵

任务	示例	复杂性
类别层次结构	商店中的产品类别	低
组织结构图	公司结构，报告链	低
BOM（物料清单）	制造产品的组成	中
菜单结构	网站导航树	低
路径查找	交付路线，关系图	高
评论树	社交媒体中的嵌套评论	中
依赖图	项目和子任务	中
可追溯性	材料来源跟踪	中

关键要点

递归CTE是处理层次数据的强大工具
结构：锚定成员 + 递归成员 + 停止条件
锚定成员定义起始行
递归成员根据先前的行添加新行
停止条件防止无限循环
实际应用：类别、组织结构、BOM、菜单、路径查找
性能：结构越简单，执行越快
替代方案：可以使用过程代码，但递归CTE通常更简单、更清晰

递归CTE将复杂的层次查询从难题转变为可理解的SQL。它们是任何处理树和网络数据结构的人的不可或缺的工具。

在后续课程中，我们将探讨高级优化技术和专用SQL函数。

练习

课程

练习场

测试

课程 6.6: 将递归CTE应用于现实世界问题

什么是递归CTE？

示例 1：类别层次结构

示例 2：组织结构图

示例 3：物料清单（BOM）

示例 4：菜单结构（菜单树）

示例 5：图中的路径查找

最佳实践与优化

1. 始终定义停止条件

2. 使用UNION ALL而不是UNION

3. 避免循环引用

4. 限制递归深度

递归CTE的应用矩阵

关键要点

登录

注册

课程 6.6: 将递归CTE应用于现实世界问题

什么是递归CTE？

示例 1：类别层次结构

示例 2：组织结构图

示例 3：物料清单（BOM）

示例 4：菜单结构（菜单树）

示例 5：图中的路径查找

最佳实践与优化

1. 始终定义停止条件

2. 使用UNION ALL而不是UNION

3. 避免循环引用

4. 限制递归深度

递归CTE的应用矩阵

关键要点