三十三、Oracle层次查询 connect by prior

           Oracle层次查询 connect by prior

1、表格初始化脚本

https://download.csdn.net/download/newbie_907486852/10838835

2、connect by递归调用

select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;

select rownum,rownum+1,rownum+2 from dual where rownum <=110000;

3、connect by层级查询

select empno,mgr, ename, job, hiredate, sal, comm, deptno, level as lv
from emp
start with empno = 7839
connect by (prior empno) = mgr;


prior 在 empno前表示对empno进行升序
prior 在 empno后表示对empno进行降序
start with empno = 7839表示从这个开始查询
connect by (prior empno) = mgr; 表示进行层级查询
level 自动递增