Oracle分层查询

1.对于表中行与行存在父子关系时,可以通过connect by查询方式,查询行与行之间的父子关系

select lpad(-,(level-1),-)|| empno as tempno,
          ename,mgr,level,
          decode(level,1,1) as root,
          decode(connect_by_isleaf,1,1) as leafnode
 from emp
start with empno=7566
connect by (prior empno) = mgr
order by level;

除了level伪列外,connect_by_isleaf也是伪列,1表示没有子记录,0则相反

2.当需要把表中某一属性按照父子关系拼接起来,可以借助sys_connect_by_path

with x1 as
   (select deptno,ename,row_number() over(partition by deptno order by ename) as rn from emp1)
select deptno,sys_connect_by_path(ename,,) as nameC from x1
  where connect_by_isleaf =1  
  start with rn=1 connect by (prior deptno)= deptno and (prior rn)=rn-1;

3.对于树形查询结果排序,如果保持树结构的正确性非常关键,因此我们采用SIBLINGS关键字只对分支内部进行排序

select lpad(-,(level-1),-)|| empno as tempno,
          ename,mgr,level,
          decode(level,1,1) as root,
          decode(connect_by_isleaf,1,1) as leafnode
 from emp1
start with empno=7566
connect by (prior empno) = mgr
order siblings by empno desc ;

对于非父子关系字段的过滤条件,需要为该字段的过滤条件做一个子查询,然后基于该子查询再进行树形查询。

4.上面几个查询都是从根往子节点查询,如何从子节点递归查询到根呢,如下所示

select ename,mgr,level 
 from emp1
start with empno=7902
connect by (prior mgr) = empno
order by level;

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。