创建调试数据

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    salary DECIMAL(10, 2)
);
INSERT INTO employees (id, name, manager_id, salary) VALUES
(1, 'Alice', NULL, 50000.00),
(2, 'Bob', 1, 40000.00),
(3, 'Charlie', 1, 45000.00),
(4, 'Dave', 2, 35000.00),
(5, 'Eve', 3, 30000.00),
(6, 'Frank', 3, 32000.00),
(7, 'Grace', 6, 28000.00),
(8, 'Henry', 6, 29000.00),
(9, 'Ivy', 8, 25000.00),
(10, 'Jack', 8, 26000.00);

给定数据，向下级联查询

注意看第四行和第八行的写法！！！！！！！！！！

WITH RECURSIVE employee_tree AS (
    SELECT id, name, manager_id, salary, 0 AS level
    FROM employees
    WHERE manager_id = 3
    UNION ALL
    SELECT e.id, e.name, e.manager_id, e.salary, et.level + 1
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.id
)
SELECT id, name, manager_id, salary, level
FROM employee_tree
ORDER BY level, id;

给定数据，向上级联查询

注意看第四行和第八行的写法！！！！！！！！！！

WITH RECURSIVE employee_tree AS (
    SELECT id, name, manager_id, salary, 0 AS level
    FROM employees
    WHERE id = 3
    UNION ALL
    SELECT e.id, e.name, e.manager_id, e.salary, et.level + 1
    FROM employees e
    JOIN employee_tree et ON e.id = et.manager_id
)
SELECT id, name, manager_id, salary, level
FROM employee_tree
ORDER BY level, id;