mysql 父子节点查询

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

WITH RECURSIVE category_tree AS (
    -- 初始查询：选择根节点
    SELECT id, name, parent_id
    FROM categories
    WHERE id = ? -- 替换为你要查询的节点ID

    UNION ALL

    -- 递归查询：选择子节点
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

SELECT c1.id, c1.name, c1.parent_id
FROM categories c1
JOIN categories c2 ON c1.parent_id = c2.id
WHERE c2.id = ? -- 替换为你要查询的节点ID
UNION ALL
SELECT id, name, parent_id
FROM categories
WHERE id = ?;