mysql 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', 2),
(4, 'David', 2),
(5, 'Eve', 1);

WITH RECURSIVE employee_tree AS (
    -- 初始查询（锚点）
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 2 -- 查询Bob及其下属

    UNION ALL

    -- 递归查询（递归部分）
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree;

WITH RECURSIVE employee_tree AS (
    -- 初始查询（锚点）
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE id = 2 -- 查询Bob及其下属

    UNION ALL

    -- 递归查询（递归部分）
    SELECT e.id, e.name, e.manager_id, et.depth + 1
    FROM employees e
    INNER JOIN employee_tree et ON e.manager_id = et.id
    WHERE et.depth < 3 -- 限制递归深度为3
)
SELECT * FROM employee_tree;