现在有个表 a
parentid代表父节点 detail为级别 1代表明细 0代表非明细
id parentid detail
1 -1 0
101 1 0
10101 101 0
1010101 10101 1
102 1 0
10201 102 1
我想找到某个明细的所有父节点一直递归到最上一层跟节点
例如 1010101为明细结点 怎样把10101,101,1这这几条记录找出来呢
也就是找到某个明细节点的所有的N层父结点谢谢大家啦 急!!!
parentid代表父节点 detail为级别 1代表明细 0代表非明细
id parentid detail
1 -1 0
101 1 0
10101 101 0
1010101 10101 1
102 1 0
10201 102 1
我想找到某个明细的所有父节点一直递归到最上一层跟节点
例如 1010101为明细结点 怎样把10101,101,1这这几条记录找出来呢
也就是找到某个明细节点的所有的N层父结点谢谢大家啦 急!!!
9i以上的話
可以用start with ... connect by prior ... 的語法
並有LEVEL的觀念create table XIE_20070623 as
(
(select 1 id, -1 parentid, 0 detail from dual)
union
(select 101 id, 1 parentid, 0 detail from dual)
union
(select 10101 id, 101 parentid, 0 detail from dual)
union
(select 1010101 id, 10101 parentid, 1 detail from dual)
union
(select 102 id, 1 parentid, 0 detail from dual)
union
(select 10201 id, 102 parentid, 1 detail from dual)
)
/SQL> select * from XIE_20070623
2 / ID PARENTID DETAIL
--------- --------- ---------
1 -1 0
101 1 0
102 1 0
10101 101 0
10201 102 1
1010101 10101 16 rows selected.SQL> select id,parentid,detail,LEVEL
2 from XIE_20070623
3 start with id=10101
4 connect by prior parentid=id
5 / ID PARENTID DETAIL LEVEL
--------- --------- --------- ---------
10101 101 0 1
101 1 0 2
1 -1 0 3
可由LEVEL清楚看出關係SQL> select id,parentid,detail,LEVEL
2 from XIE_20070623
3 start with parentid=-1
4 connect by prior id=parentid
5 / ID PARENTID DETAIL LEVEL
--------- --------- --------- ---------
1 -1 0 1
101 1 0 2
10101 101 0 3
1010101 10101 1 4
102 1 0 2
10201 102 1 3