Oracle 树操作、递归查询(select…start with…connect by…prior)_start with connect by prior

Oracle 树操作、递归查询(select…start with…connect by…prior)

一、Oracle中start with…connect by prior子句用法
connect by 是结构化查询中用到的，其基本语法是：
select … from tablename
where 条件1, 可选;
connect by 条件2，是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR empno = mgr；就是说上一条记录的empno 是本条记录的mgr，即本记录的父亲是上一条记录。
start with 条件3,  可选，是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

 

--lpad( string, padded_length, [ pad_string ] )
----string 准备被填充的字符串，长度不能为空--为空时无效果；
----padded_length 填充之后的字符串长度，也就是该函数返回的字符串长度，
----              如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
----pad_string 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，
----           如果这个参数未写，lpad函数将会在string的左边粘贴空格。
----LEVEL 根节点的层号始终为1，根节点的子节点为2， 依此类推。
select lpad('@',2*(level-1)+1,'*')||empno tree_show,level Level_NO,connect_by_isleaf is_leaf,emp.*
from emp
where sal > 500 --where 条件可选
connect by prior empno = mgr 
start with mgr is null;

LEVEL: 
    层号根据节点与起始节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，起始节点的子节点为2， 依此类推。

connect_by_isleaf:
    此函数用来判断当前节点是否包含下级节点，如果包含的话，说明不是叶子节点，返回0；反之，如果不包含下级节点，这里返回1。

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

sys_connect_by_path
    此函数将遍历到的路径根据函数中的分隔符，组成一个新的字符串，这个功能还是很强大的,如下:

select lpad('@', 2 * (level - 1) + 1, '*') || empno tree_show,
       sys_connect_by_path(empno, '/'),
       level Level_NO,
       connect_by_isleaf is_leaf
  from emp
 where sal > 500 --where 条件可选
connect by prior empno = mgr
 start with mgr is null;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

----用with语句给临时表起个别名 tme_table, 然后用别名tmp_table代替SQL语句的查询结果集，即临时表

with tmp_table as
 (select lpad('@', 2 * (level - 1) + 1, '*') || empno tree_show,
         sys_connect_by_path(empno, '/'),
         empno,
         mgr,
         level Level_NO,
         connect_by_isleaf is_leaf
    from emp
   where sal > 500 --where过滤条件，可选
  connect by prior empno = mgr
   start with mgr is null)
select *
  from tmp_table
 where LEVEL_NO = (select LEVEL_NO from tmp_table where empno = 7566);
----查询给定员工empno=7566同级别的其他员工

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

----求与指定empno=7369父节点同级别的empno

with tmp_table as
 (select lpad('@', 8 * (level - 1) + 1, '*') || empno tree_show,
         sys_connect_by_path(empno, '/'),
         empno,ename,mgr,level Level_NO,
         connect_by_isleaf is_leaf
    from emp
  connect by prior empno = mgr
   start with mgr is null)
select *
  from tmp_table
 where level_NO =
       (select level_NO
          from tmp_table
         where empno = (select mgr from tmp_table where empno = 7369));

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

----求与指定empno=7369父节点同级别且拥有共同父节点的empno --2条记录

---备注：与指定empno=7369父节点同级别的empno--8条记录

with tmp_table as
 (select lpad('@', 8 * (level - 1) + 1, '*') || empno tree_show,
         sys_connect_by_path(empno, '/'),
         empno,ename,mgr,level Level_NO,
         connect_by_isleaf is_leaf
    from emp
  connect by prior empno = mgr
   start with mgr is null)
select *
  from tmp_table
 where mgr =
       (select mgr
          from tmp_table
         where empno = (select mgr from tmp_table where empno = 7369));

 

以上参考  https://www.cnblogs.com/yingsong/p/5035907.html