mysql 树形展示

WITH RECURSIVE cte AS (
    SELECT * FROM your_table WHERE id = your_node_id
    UNION ALL
    SELECT t.* FROM your_table t INNER JOIN cte ON t.parent_id = cte.id
)
SELECT * FROM cte;

WITH RECURSIVE cte AS (
    SELECT * FROM your_table WHERE id = your_node_id
    UNION ALL
    SELECT t.* FROM your_table t INNER JOIN cte ON t.id = cte.parent_id
)
SELECT * FROM cte;

-- 创建表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 插入数据
INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Computers', 1),
('Laptops', 2),
('Desktops', 2),
('Smartphones', 1),
('Cameras', 1);

-- 查询某个节点的所有子节点
WITH RECURSIVE cte AS (
    SELECT * FROM categories WHERE id = 2
    UNION ALL
    SELECT t.* FROM categories t INNER JOIN cte ON t.parent_id = cte.id
)
SELECT * FROM cte;