mysql 所有子节点

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

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

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    lft INT,
    rgt INT
);

SELECT child.*
FROM categories AS parent, categories AS child
WHERE child.lft BETWEEN parent.lft AND parent.rgt
AND parent.id = ? -- 这里的问号代表你要查询的类别ID
ORDER BY child.lft;