跳到主要内容

MySQL 递归查询

在MySQL中，递归查询是一种强大的工具，用于处理具有层次结构的数据。递归查询允许你在一个表中查询数据，并根据某些条件反复引用自身，直到满足特定条件为止。这种查询方式特别适用于处理树形结构、组织结构或任何具有父子关系的数据。

什么是递归查询？​

递归查询是一种查询方式，它通过反复引用自身来处理层次结构数据。在MySQL中，递归查询通常通过**公共表表达式（CTE，Common Table Expressions）**来实现。CTE允许你定义一个临时的结果集，然后在查询中引用它。

递归查询的核心思想是：

  1. 基础查询：定义递归的起点。
  2. 递归部分：基于基础查询的结果，反复引用自身，直到满足终止条件。

递归查询的基本语法​

在MySQL中，递归查询的基本语法如下：

sql
WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT ... FROM ... WHERE ...
    UNION ALL
    -- 递归部分
    SELECT ... FROM cte_name, ... WHERE ...
)
SELECT * FROM cte_name;
  • WITH RECURSIVE：声明一个递归的CTE。
  • cte_name：CTE的名称。
  • 基础查询：定义递归的起点。
  • 递归部分：基于基础查询的结果，反复引用自身。

递归查询的示例​

假设我们有一个表示组织结构的表 employees，其中包含员工的ID、姓名和上级ID：

sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 3);

在这个表中，manager_id 表示员工的上级ID。如果 manager_id 为 NULL，则表示该员工是顶级管理者。

示例1：查找某个员工的所有下属​

假设我们想要查找 Alice 的所有下属（包括直接和间接下属），可以使用递归查询：

sql
WITH RECURSIVE subordinates AS (
    -- 基础查询：找到Alice的直接下属
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id = 1
    UNION ALL
    -- 递归部分：找到下属的下属
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

输出结果：

idnamemanager_id
2Bob1
3Charlie1
4David2
5Eve3

示例2：查找某个员工的所有上级​

假设我们想要查找 David 的所有上级（包括直接和间接上级），可以使用递归查询：

sql
WITH RECURSIVE managers AS (
    -- 基础查询：找到David的直接上级
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 4
    UNION ALL
    -- 递归部分：找到上级的上级
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN managers m ON e.id = m.manager_id
)
SELECT * FROM managers;

输出结果：

idnamemanager_id
4David2
2Bob1
1AliceNULL

递归查询的实际应用场景​

递归查询在许多实际场景中非常有用，例如：

  1. 组织结构：查找某个员工的所有下属或上级。
  2. 文件系统：查找某个文件夹下的所有子文件夹和文件。
  3. 分类层次：查找某个分类的所有子分类。
  4. 社交网络：查找某个用户的所有朋友或关注者。

总结​

递归查询是MySQL中处理层次结构数据的强大工具。通过使用递归CTE，你可以轻松地查询树形结构、组织结构或任何具有父子关系的数据。递归查询的核心在于定义基础查询和递归部分，并通过反复引用自身来处理数据。

附加资源与练习​

  • 练习1：尝试在 employees 表中添加更多员工，并使用递归查询查找某个员工的所有下属。
  • 练习2：修改递归查询，使其能够查找某个员工的所有上级，并显示他们的层级关系（例如，顶级管理者为1级，直接上级为2级，依此类推）。
  • 参考文档：MySQL官方文档 - 递归CTE
提示

递归查询在处理层次结构数据时非常有用，但要注意避免无限递归。确保你的递归部分有明确的终止条件。