如何使用Oracle CONNECT BY将层次结构中的所有值链接到某个值

1   3   
  / 
  2   4
   
    7   5     8
      /     /
      6     9

表是：

select 2 child,1 father from dual
union all
select 2 child,3 father from dual
union all
select 4 child,3 father from dual
union all
select 7 child,2 father from dual
union all
select 6 child,5 father from dual
union all
select 6 child,7 father from dual
union all
select 9 child,8 father from dual

如何将所有值与值CHILD或FATHER = 2相关联？

肯定是

1,2,3,4,5,6,7

并不是

8,9

因为它与价值2无关.

如何通过使用CONNECT BY语句来实现这一点？谢谢.

附：这个解决方案离我很近但不适用于我的模型：

Find all nodes in an adjacency list model with oracle connect by

数据库版本 – 10.2.0.5.0

模型与甲骨文 – 连接 – 通过

所以,大概的策略可能是这样的(例如从node = 7开始)：

第1步(方向=向上)

select t1.father,connect_by_root father as root,connect_by_isleaf from 
(my_table) t1
start with father=7
connect by prior father = child

结果是7,1,其中1,3是高级根(isleaf = 1)

第2步(获取1,3方向的路线=向下)

select t1.child,connect_by_isleaf from 
(my_table) t1
start with father=1
connect by father = prior child

结果是2,7,其中6是低级根(isleaf = 1)

select t1.child,connect_by_isleaf from 
(my_table) t1
start with father=3
connect by father = prior child

结果是2,4其中6,4是低级根(isleaf = 1)

第3步(获得6,4方向的路线=向上)

select t1.father,connect_by_isleaf from 
(my_table) t1
start with child=6
connect by prior father = child

结果是5,3其中5,3是高级根(isleaf = 1)
这个结果我发现node = 5

然后我必须改变方向向下..然后再次向上..然后再次下降..

但如何在一个选择中结合所有这些步骤？对于初学者来说很难.请帮帮我.

with h as (
                     SELECT 2 child,1 father FROM dual
                     UNION ALL
                     SELECT 2 child,3 father FROM dual
                     UNION ALL
                     SELECT 4 child,3 father FROM dual
                     UNION ALL
                     SELECT 7 child,2 father FROM dual
                     UNION ALL
                     SELECT 6 child,5 father FROM dual
                     UNION ALL
                     SELECT 6 child,7 father FROM dual
                     UNION ALL
                     SELECT 9 child,8 father FROM dual
            ),bi as (select * from h union all select father,child from h )     
    select distinct father from bi
    start with child = 2
    connect by nocycle
    prior father = child

我在查询中使用’with’表示法以获得更好的可读性.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!