Oracle Connect By 浅析

Oracle Connect By 浅析

树形图谱

测试数据及脚本

create table jc_test_001(
p_node int,-- 父节点
c_node int -- 子节点
);
insert into jc_tet_001 values(null,20);
insert into jc_tet_001 values(20,19);
insert into jc_tet_001 values(20,11);
insert into jc_tet_001 values(20,22);
insert into jc_tet_001 values(20,23);
insert into jc_tet_001 values(19,5);
insert into jc_tet_001 values(19,8);
insert into jc_tet_001 values(19,7);
insert into jc_tet_001 values(22,40);
insert into jc_tet_001 values(40,1);
insert into jc_tet_001 values(8,3);
insert into jc_tet_001 values(8,9);

自根向叶遍历

select p_node,c_node, level
from jc_test_001
start with c_node=20
connect by p_node = prior c_node
order by level;

自叶向根遍历

select p_node,c_node, level
from jc_test_001
start with c_node=9
connect by prior p_node = c_node
order by level;