postgres-具有递归

Question

我期望以下内容返回所有元组，将层次结构中的每个父级解析到顶部，但它仅返回最低级别（在查询中指定了其ID）。如何为给定的level_id返回整棵树？

create table level(
level_id int,
level_name text,
parent_level int);

 insert into level values (197,'child',177), (  177, 'parent', 3 ), (  2, 'grandparent',  null  );

WITH RECURSIVE recursetree(level_id, levelparent) AS (
 SELECT level_id, parent_level 
 FROM level 
 where level_id = 197

UNION ALL
SELECT t.level_id, t.parent_level
FROM level t, recursetree rt 
WHERE rt.level_id = t.parent_level
)

SELECT * FROM recursetree;

Answer 1

首先，如果您确实是祖父母，则(2, 'grandparent', null)应该如此(3, 'grandparent', null)。其次，您在查询的递归一半中的（隐式）连接条件是向后的，您想让父级脱离，rt.levelparent而不是t.parent_level：

WITH RECURSIVE recursetree(level_id, levelparent) AS (
    SELECT level_id, parent_level 
    FROM level 
    WHERE level_id = 197

    UNION ALL

    SELECT t.level_id, t.parent_level
    FROM level t JOIN recursetree rt ON rt.levelparent = t.level_id
    -- join condition fixed and ANSI-ified above
)
SELECT * FROM recursetree;