mysql中递归的使用 WITH RECURSIVE

MySQL递归查询的基本语法和用法

MySQL 8.0及以上版本支持使用WITH RECURSIVE来进行递归查询。WITH RECURSIVE定义了一个递归的公用表表达式（CTE），它包含两个部分：递归的基础部分（非递归部分）和递归部分。

WITH RECURSIVE cte_name AS (
    -- 基础部分（非递归部分）
    SELECT ...
    UNION ALL
    -- 递归部分
    SELECT ...
    FROM cte_name ...
)
SELECT * FROM cte_name;

‌基础部分‌：这是递归查询的起点，它返回递归查询的初始结果集。
‌递归部分‌：这是递归查询的核心，它基于前一个结果集生成新的结果集，直到没有新的结果集产生为止。

实践编写MySQL递归SQL查询语句

假设我们有一个表示组织结构的表employees，结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT -- 上级员工的ID，自引用
);

查询某个员工（比如ID为1的员工）及其所有下属。

WITH RECURSIVE subordinates AS (
    -- 基础部分：查询初始员工（ID为1）
    SELECT id, name, manager_id
    FROM employees
    WHERE 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;

示例二

根据任务的部门id找顶级部门

WITH RECURSIVE parent_dept AS (
    -- 初始查询，选择起始部门
    SELECT id, name, parent_id
    FROM system_dept
    WHERE id = 21 -- 要查询的部门id
    UNION ALL
    -- 递归查询，选择所有上级部门
    SELECT d.id, d.name, d.parent_id
    FROM system_dept d
    INNER JOIN parent_dept pd on d.id = pd.parent_id
)
SELECT id
FROM parent_dept
where parent_id = 0 -- 留下顶级部门id