17,086
社区成员
发帖
与我相关
我的任务
分享
--9i在层级的处理就是没有10G来的方便
with a as(
select '1' as id, 'A' as code, '0' as parent_id from dual
union
select '2', 'B', '1' from dual
union
select '3', 'C', '2' from dual
union
select '4', 'D', '2' from dual
union
select '5', 'E', '1' from dual
union
select '6', 'F', '5' from dual
union
select '7', 'G', '6' from dual
)
select path from
(
select path,rn,nvl(lead(rn,1)over(order by 1),0) rm
from
(
select substr(sys_connect_by_path(code, '-'),2) Path ,level rn from a
--where level >=3
start with parent_id = '0'
connect by prior id = parent_id
)
)
where rn>=rm
--result:
1 A-B-C
2 A-B-D
3 A-E-F-G