SQL的高级查询

--12.1  简单的树形查询

select  level,
    empno,
    ename,
    mgr,
    (prior ename )  as mgr_name
from emp   
start with mgr is null
connect by (prior empno ) = mgr
ORDER BY 1;

 

--12.2根节点 root_node、分支节点 branch_node、叶子节点 leaf_node

select lpad('=', (level - 1) * 2, '--') || empno as empno,
        ename,
        mgr,
        level,
        decode(level, 1, 1) as root_node,
        case
          when (connect_by_isleaf = 0 and level > 1) then
           1
          end as branch_node,
       decode(connect_by_isleaf, 1, 1) as leaf_node
  from emp
 start with empno = 7839
connect by (prior empno) = mgr
 ORDER BY 4, 5, 6, 7;