oracle start with connect by prior用法

oracle start with connect by prior用法

作用：显示树形表结构的信息
测试脚本：

create table a_test
 ( parentid varchar2(10),
   subid    varchar2(10)
);

insert into a_test values ( '1', '2' );
insert into a_test values ( '1', '3' );
insert into a_test values ( '2', '4' );
insert into a_test values ( '2', '5' );
insert into a_test values ( '3', '6' );
insert into a_test values ( '3', '7' );
insert into a_test values ( '5', '8' );
insert into a_test values ( '5', '9' );
insert into a_test values ( '7', '10' );
insert into a_test values ( '7', '11' );
insert into a_test values ( '10', '12' );
insert into a_test values ( '10', '13' );
 
 commit;
 
 select * from a_test;

表数据

树形结构

start with ：将查询表中的树型结构关系
level：表示层级
CONNECT BY {PRIOR 列名1=列名2}：列名1为子，列名2为父，则为子节点树
CONNECT BY {列名1=PRIOR 裂名2}：列名1为子，列名2为父，则为父节点树
where：限制区域，在start with前面

查询7的父节点
select t.parentid,t.subid,level from a_test t
start with t.subid = '7'
connect by subid = PRIOR parentid
ORDER BY level desc;
查询7的子节点
select t.parentid,t.subid,level from a_test t
start with t.parentid = '7'
connect by PRIOR subid = parentid
ORDER BY level DESC