mysql如何递归查询

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

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = ? -- 替换为具体的员工ID
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.id = eh.manager_id
)
SELECT * FROM employee_hierarchy;

SELECT e1.id, e1.name, e2.id AS manager_id, e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id
WHERE e1.id = ? -- 替换为具体的员工ID
UNION ALL
SELECT e1.id, e1.name, e2.id AS manager_id, e2.name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id
WHERE e2.manager_id IS NOT NULL;

SET GLOBAL innodb_lock_wait_timeout = 120; -- 设置为120秒