如何使用oracle-10g在层次结构查询中获取根祖先?

问题描述

表非常简单，pid表示父级id，而cid表示子级id.表中可能有不止一棵树.所以我的问题是:
知道 几个 cid，我们如何才能获得根祖先
这是一个例子

the table is very simple,pid means the parent id,and cid means the child id.And there may be more than one trees in the table.So my question is:
knowing several cid,how can we get the root ancestors
here is an example

pid cid
1 2
2 3
3 4
5 6
6 7
7 8

pid cid
1 2
2 3
3 4
5 6
6 7
7 8

给定cid = 4或cid = 8，我想获得其pid为1 ro 5的根祖先.
最后，我使用的是 oracle 10g

given cid = 4 or cid = 8,I want to get their root ancestors whose pid is 1 ro 5
finally,I'm using an oracle 10g

推荐答案

select 
  t1.cid,
  connect_by_root(t1.pid) as root
from 
  your_table t1
  left join your_table t2
    on t2.cid = t1.pid
where t1.cid in (4, 8)
start with t2.cid is null
connect by t1.pid = prior t1.cid

小提琴

这篇关于如何使用oracle-10g在层次结构查询中获取根祖先?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！