oracle 递归 通过子节点查根节点

根节点的thelevel 应该为null，可以加个条件 where sectype.thelevel is null 。

SELECT sectype.thekey, sectype.name, sectype.thelevel

FROM sectype

where sectype.thelevel is null

start with sectype.thekey = '8565'

connect by prior sectype.thelevel = sectype.thekey

1、创建测试表，create table test_connect(id number, p_id number)

2、插入测试数据，

insert into test_connect values(1,1)

insert into test_connect values(2,1)

insert into test_connect values(3,2)

insert into test_connect values(4,3)

commit

3、查询数据表内容，select * from test_connect ，

4、执行递归查询语句，加入nocycle要素，不会出现【ORA-01436: 用户数据中的 CONNECT BY 循环的错误】，执行结果如下，

select *

from test_connect t

start with id = 4

connect by nocycle prior t.p_id = t.id

---