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 44select 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...
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 44select 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...
觉得这个字段,最高不应该这样算出来,应该是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))