Oracle CONNECT BY递归子级到父级查询，包括自我引用的最终父级 [英] Oracle CONNECT BY recursive child to parent query, include ultimate parent that self references

问题描述

在以下示例中

id parent_id
A  A
B  A
C  B

select id, parent_id
from table
start with id = 'A'
connect by nocycle parent_id = prior id

我知道

A A
B A
C B

在我的数据库中，表中有数百万行，并且层次结构又深又宽，我对所有子级都不感兴趣.我可以派生出我感兴趣的孩子.因此，我想打开查询并向START WITH提供孩子ID.然后，我想递归输出父级，直到到达顶部.在我的情况下，顶部是id和parent_id相等的地方.这是我正在尝试的方法，但是我无法让它显示顶级父级.

In my database I have millions of rows in the table and deep and wide hierarchies and I'm not interested in all children. I can derive the children I'm interested in. So I want to turn the query on its head and supply START WITH with the children ids. I then want to output the parent recursively until I get to the top. In my case the top is where the id and parent_id are equal. This is what I'm trying, but I can't get it to show the top level parent.

select id, parent_id
from table
START WITH id = 'C'
CONNECT BY nocycle id = PRIOR parent_id

这给了我

C B
B A

它没有输出AA.可以这样做吗?我希望不会在输出中将parent_id显示为单独的列，而只是显示与该ID相关的名称.该顺序将隐含层次结构.

It's not outputting the A A. Is it possible to do this? What I'm hoping to do is not show the parent_id as a separate column in the output, but just show the name relating to the id. The hierarchy is then implied by the order.

推荐答案

我通过使用 WITH 子句获得了该结果.

I got that result by using WITH clause.

WITH REC_TABLE ( ID, PARENT_ID)
AS
(
    --Start WITH 
    SELECT ID, PARENT_ID
    FROM table
    WHERE ID='C'

    UNION ALL
    --Recursive Block
    SELECT T.ID, T.PARENT_ID
    FROM table T 
    JOIN REC_TABLE R
    ON R.PARENT_ID=T.ID
    AND R.PARENT_ID!=R.ID   --NoCycle rule
)
SELECT *
FROM REC_TABLE;

它似乎也可以工作.

select id, parent_id
from T
START WITH id = 'C'
CONNECT BY id = PRIOR parent_id and parent_id!= prior id;
--                                  ^^^^^^^^^^^^^^^^^^^^
--                                      break cycles

希望有帮助.

这篇关于Oracle CONNECT BY递归子级到父级查询，包括自我引用的最终父级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！