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
    INNER JOIN category_tree ct ON c.id = ct.parent_id
)
SELECT * FROM category_tree WHERE parent_id IS NOT NULL;

SELECT c.id, c.name
FROM categories c
INNER JOIN categories p ON c.id = p.parent_id
WHERE c.id = ?; -- 替换为你要查询的id

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = ?
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_tree ct ON c.id = ct.parent_id
    LIMIT 10 -- 限制递归深度
)
SELECT * FROM category_tree WHERE parent_id IS NOT NULL;

CREATE INDEX idx_parent_id ON categories(parent_id);

SELECT c.id, c.name
FROM categories c
INNER JOIN categories p ON c.id = p.parent_id
WHERE c.id = ?
LIMIT 10; -- 分页查询