Postgresql查询获取存储在单个表中的n级父子关系

使用PostgreSQL查询获取存储在单个表中的n级父子关系

sql
CREATE TABLE hierarchy (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES hierarchy(id)
);

sql
WITH RECURSIVE hierarchy_recursive AS (
    SELECT id, name, parent_id, 1 as level
    FROM hierarchy
    WHERE id = :desired_node_id
    UNION ALL
    SELECT h.id, h.name, h.parent_id, hr.level + 1
    FROM hierarchy h
    JOIN hierarchy_recursive hr ON h.id = hr.parent_id
)
SELECT * FROM hierarchy_recursive;

sql
INSERT INTO hierarchy (name, parent_id) VALUES
('Root', NULL),
('Child1', 1),
('Child2', 1),
('Grandchild1', 2),
('Grandchild2', 2);

sql
WITH RECURSIVE hierarchy_recursive AS (
    SELECT id, name, parent_id, 1 as level
    FROM hierarchy
    WHERE id = 3
    UNION ALL
    SELECT h.id, h.name, h.parent_id, hr.level + 1
    FROM hierarchy h
    JOIN hierarchy_recursive hr ON h.id = hr.parent_id
)
SELECT * FROM hierarchy_recursive;