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

Question

关系模型是

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

模型与oracle-connect-by

所以，大致的策略可能是这样的(例如从 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,2,1,3 其中 1,3 是高级根 (isleaf=1)

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

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

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

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

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

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

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

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

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

但是如何将所有这些步骤合并到一个选择中？初学者很难。请帮帮我。

Answer 1

对于您的输出，您不需要定向图形，因此将反向链接添加到所有现有链接。这就是我在子查询“bi”中所做的。然后您使用 nocyle 按查询连接。

    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”表示法以提高可读性。