postgres-with recursive[英] postgres - with recursive

本文是小编为大家收集整理的关于postgres-with recursive的处理方法,想解了postgres-with recursive的问题怎么解决?postgres-with recursive问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我期望以下返回所有元组,将层次结构中的每个父级解析到顶部,但它只返回最低级别(其 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;

推荐答案

首先,你的(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;

本文地址:https://www.itbaoku.cn/post/1764032.html