connect表连接(含start)

-- 显示从e.mgr is null开始的管理关系,及所在层
select e.ename,e.empno,e.mgr,level "所在层"  --level 所在层
from emp e
start with e.mgr is null -- 从mgr为空开始
connect by e.mgr= prior e.empno  --当前的人员的上级工号是上前一行的人的工号

--各个层的总人数
select level,count(level) "本层人数"
from emp e
start with e.mgr is null
connect by e.mgr= prior e.empno
group by level

--渐进显示管理层次
SELECT LEVEL, LPAD('  ',3*(LEVEL - 1)) || e.ename "EMPLOYEE",e.empno,e.mgr
FROM EMP e
START WITH e.mgr IS NULL
CONNECT BY PRIOR e.empno =e.mgr ;

--控制显示层
select e.ename,e.empno,e.mgr,level "所在层"  ,e.deptno
from emp e
where level >2
start with e.mgr is null
connect by e.mgr= prior e.empno

--多个连接条件
select e.ename,e.empno,e.mgr,level "所在层"  ,e.deptno
from emp e
start with e.mgr is null
connect by e.mgr= prior e.empno and e.deptno=prior e.deptno

--子查询,复查询交叉
select  t2.empno,t2.ename,
        (select count(*)
         from emp t1
         start with t1.empno=t2.empno              --子父查询交叉条件控制
         connect by t1.mgr=prior t1.empno
         ) "统领数"
from emp t2

select e.ename,e.empno,e.mgr "mgr.no",prior e.ename "mgr.name" ---该员工的上级 编号,姓名
from emp e
start with e.mgr is null -- 从mgr为空开始
connect by e.mgr= prior e.empno  --当前的人员的上级工号是上前一行的人的工号