oracle：数据库的级联查询：Connect by

创建测试表，增加测试数据

create table test(superid varchar2(20),id varchar2(20));

insert into test values('0','1');
insert into test values('0','2');

insert into test values('1','11');
insert into test values('1','12');

insert into test values('2','21');
insert into test values('2','22');

insert into test values('11','111');
insert into test values('11','112');

insert into test values('12','121');
insert into test values('12','122');

insert into test values('21','211');
insert into test values('21','212');

insert into test values('22','221');
insert into test values('22','222');

commit;

在上表中查找id=11相关联的所有的父节点以及所有的子节点数据，级联sql如下：

select id,superid from TEST t start with id='11' connect by prior superId = id
UNION
select id,superid from TEST t start with id='11' connect by prior id = superId

查询结果如下图：

在上表中查找id=11相关联的所有的父节点数据，级联sql如下：

select id,superid from TEST t start with id='11' connect by prior superId = id

查询结果如下图：

在上表中查找id=11相关联的所有的子节点数据，级联sql如下：

select id,superid from TEST t start with id='11' connect by prior id = superId

查询结果如下图：

在上表中查找id=1的本身级子级为2层的数据 ，级联sql如下：

select id,superid from TEST t start with id='1' connect by prior id = superId and level <= 2

查询结果如下图：