mysql向下递归查询

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

WITH RECURSIVE employee_tree 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 employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree;

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

DELIMITER //

CREATE PROCEDURE GetSubordinates(IN employee_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(100);
    DECLARE v_manager_id INT;
    DECLARE cur CURSOR FOR SELECT id, name, manager_id FROM employees WHERE manager_id = employee_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 输出根节点
    SELECT id, name, manager_id FROM employees WHERE id = employee_id;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_id, v_name, v_manager_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 递归调用存储过程
        CALL GetSubordinates(v_id);
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

CALL GetSubordinates(1);