PostgreSQL - 使用递归查询

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;

- Black

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;

- mu is too short

---

如何让所有子元素处于同一层级？目前我只能在每个层级上获取一行。 - Michal Zimmermann

@zimmi：不确定你的意思是什么。 - mu is too short

假设我有两行数据，它们具有相同的parent_id。使用这段代码，结果集中只会返回其中一行。 - Michal Zimmermann

@zimmi：您应该从用户(User)开始，反转链接方向：http://sqlfiddle.com/#!15/ac381/3 - mu is too short

这是一个有趣的想法，但是我需要想办法停止递归。如果我的角色链类似于用户->经理->管理员->超级管理员，并且我作为管理员登录，我不应该在结果中看到超级管理员角色。 - Michal Zimmermann