Oracle 递归函数 start with ..... connect by prior

select ... from tableName
where Conditional1
start with  Conditional2
connect by Conditional3
;

<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：查询结果重起始根结点的限定条件。
<Conditional-3>：连接条件

 

例如：

数据库表结构如下：

create table t2(
root_id number,
id number,
name varchar(5),
description varchar(10)
);

insert into t2(root_id,id,name,description)values(0,1,'a','aaa');
insert into t2(root_id,id,name,description)values(1,2,'a1','aaa1');
insert into t2(root_id,id,name,description)values(1,3,'a2','aaa2');
insert into t2(root_id,id,name,description)values(0,4,'b','bbb');
insert into t2(root_id,id,name,description)values(4,5,'b1','bbb1');
insert into t2(root_id,id,name,description)values(4,6,'b2','bbb2');

insert into t2(root_id,id,name,description)values(1,7,'a1','aaa1');
insert into t2(root_id,id,name,description)values(2,8,'a2','aaa2');
insert into t2(root_id,id,name,description)values(3,9,'b','bbb');
insert into t2(root_id,id,name,description)values(4,10,'b1','bbb1');

select * from t2  where 1=1 start with root_id=0 connect by prior id=root_id得到所有数据

select * from t2  where 1=1 start with id=1 connect by prior id=root_id

select * from t2 start with id=4 connect by prior id=root_id

 

如果不加上 prior 说明不进行下级的递归

如：select * from t2 start with id=4 connect by id=root_id