--建表
create table TEST
(
ID VARCHAR2(10),
PID VARCHAR2(10),
NAME VARCHAR2(100),
LEV VARCHAR2(100)
)
--插入数据后
rowno ID PID NAME LEV
1 a aa 1
2 b a bb 2
3 b1 a bb1 2
4 b2 a bb2 2
5 c b cc 3
6 c1 b cc1 3
7 d c dd 4
8 e d ee 5
9 f e ff 6
--- 查询
with tree(id,pid,name,lev) as
(
select id,pid,name,lev from test where id= 'a' --当前id
union all
select a.id,a.pid,a.name,a.lev from test a
inner join tree b on a.pid=b.id where a.lev <= 4 --级别范围,当前所在级别计算得出
)
select * from tree ;