connect by笔记

Oracle高阶之 connect by

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')

SELECT * FROM t2;

--prior在哪边，就表示哪列为父列
select * from t2 start WITH root_id = 0 connect by prior id = root_id;

--LEVEL伪列表示树的深度（或叫高度）。
select t2.*,level from t2 connect by prior id = root_id;
--首先，原表中ID为1-6的6行数据均为第一层（id为父列），level=1
--其次，root_id的值等于ID列的行为第二层，level=2
--所以查询结果共9行

select t2.*,LEVEL from t2 start with root_id = 0 connect by prior id = root_id;
--start with子句限定了第一层数据，即，仅筛选ROOT_ID=0的记录

--<<当表中有重复行时，这些重复行不会合并在一起，而是单独算的
insert into t2(root_id,id,name,description)values(4,6,'b2','bbb2');
COMMIT;

select t2.*,level from t2  connect by prior id = root_id order BY level,ID;
-->>

--获取特定子树
select t2.*,LEVEL from t2 start with id = 4 connect by prior id = root_id;

--prior被省略，则查询将不进行深层递归
select * from t2 start with root_id = 0 connect by id = root_id;


select t2.*,level from t2 connect by level<3;