mysql 查层级

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT
);

WITH RECURSIVE parent_categories AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = ? -- 替换为具体的节点ID
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN parent_categories pc ON c.id = pc.parent_id
)
SELECT * FROM parent_categories;

WITH RECURSIVE child_categories AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = ? -- 替换为具体的节点ID
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN child_categories cc ON c.parent_id = cc.id
)
SELECT * FROM child_categories;