CONNECT BY start with 如何带出根节点的ID

[rowland_taft]

create table test1(
IDS number(6),
PARENT_IDS number(6)
);
IDS PARENT_IDS
----------------------------
1 1 0
2 11 1
3 111 11
4 2 0
5 22 2
6 222 22
7 4 9
8 44 4
9 444 44

select c.ids
from test1 c
CONNECT BY PRIOR c.ids = c.PARENT_IDs
start with c.PARENT_IDs in (select ids from test1 where PARENT_IDS = 0)

得到
ids
---
11
111
22
222

想要得到每个id start with的根节点 root_id
想要得到如下的数据
ids root_id
------------
11 1
111 1
22 2
222 2

请教ing...

[vc555]

只要根节点，不要它的上级节点信息吗？

[hebo2005]

用 SYS_CONNECT_BY_ROOT函数获得

[rowland_taft]

9i的，10g函数用不了，只要该节点的根节点，继续请教ing...

[inthirties]

[Quote=引用楼主 rowland_taft 的帖子:]
create table test1(
IDS number(6),
PARENT_IDS number(6)
);
        IDS      PARENT_IDS
----------------------------
1 1 0
2 11 1
3 111 11
4 2 0
5 22 2
6 222 22
7 4 9
8 44 4
9 444 44

select c.ids
  from test1 c
CONNECT BY PRIOR c.ids = c.PARENT_IDs
start with c.PARENT_IDs in (select ids from test1 where PARENT_IDS = 0)

得到
ids
---
11
111
22
222

想要得到每个id start w…
[/Quote]

觉得这个字段，最高不应该这样算出来，应该是insert的时候就找好。保存进去就好了。

[白发程序猿]

select ids, substr(path, 2, instr(path, '/', 2) - 2) PARENT_IDs
  from (select c.ids, sys_connect_by_path(c.PARENT_IDs, '/') || '/' path
          from test1 c
        CONNECT BY PRIOR c.ids = c.PARENT_IDs
         start with c.PARENT_IDs in
                    (select ids from test1 where PARENT_IDS = 0))

[hebo2005]

SYS_CONNECT_BY_ROOT是9I开始有的函数