mysql 树形查询

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

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

WITH RECURSIVE employee_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, et.level + 1
    FROM employees e
    INNER JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree;

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

INSERT INTO categories (id, name, lft, rgt) VALUES
(1, 'Electronics', 1, 12),
(2, 'Computers', 2, 7),
(3, 'Laptops', 3, 6),
(4, 'Desktops', 4, 5),
(5, 'Smartphones', 8, 11),
(6, 'Apple', 9, 10);

SELECT child.*
FROM categories AS parent, categories AS child
WHERE child.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'Electronics'
ORDER BY child.lft;

WITH RECURSIVE employee_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, et.level + 1
    FROM employees e
    INNER JOIN employee_tree et ON e.manager_id = et.id
    WHERE et.level < 10 -- 限制递归深度
)
SELECT * FROM employee_tree;