postgres-递归 [英] postgres - with recursive

问题描述

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

I expected the following to return all the tuples, resolving each parent in the hierarchy up to the top, but it only returns the lowest levels (whose ID is specified in the query). How do I return the whole tree for a given 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;

推荐答案

首先，您的 （2，'祖父母'，null）如果确实是祖父母，则应为（3，'grandparent'，null）。其次，在查询的递归部分中的（隐式）连接条件是向后的，您希望从 rt.levelparent 而不是中获得父项。 t.parent_level ：

First of all, your (2, 'grandparent', null) should be (3, 'grandparent', null) if it really is a grandparent. Secondly, your (implicit) join condition in the recursive half of your query is backwards, you want to get the parent out of rt.levelparent rather than 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;

这篇关于postgres-递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！