bmysql 递归查询

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

DELIMITER //

CREATE PROCEDURE GetAllSubordinates(IN employee_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE sub_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM employees WHERE manager_id = employee_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_subordinates (id INT);

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO sub_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO temp_subordinates VALUES (sub_id);
        CALL GetAllSubordinates(sub_id);
    END LOOP;

    CLOSE cur;

    SELECT * FROM temp_subordinates;
END //

DELIMITER ;

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1 -- 假设我们要查询 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;