08.SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

--从非根节点开始遍历(只需修改start with 中的条件即可)

select level,
  lpad(‘ ‘,2 * level - 1) || ename as "Ename",
  job
from emp
start with ename = ‘SCOTT‘
connect by prior empno = mgr;

--从下向上遍历(交换connect by prior中的条件即可,使用mgr = empno)
--注意connect by prior mgr = empno 的理解
--prior表示前一条记录,即下一条返回记录的empno应当等于前一条记录的mgr

select level,
  lpad(‘ ‘,2 * level - 1) || ename as "Ename",
  job
from emp
start with ename = ‘SCOTT‘
connect by prior mgr = empno; 

--从下向上遍历(也可以将prior置于等号右边,得到相同的结果)

select level,
  lpad(‘ ‘,2 * level - 1) || ename as "Ename",
  job
from emp
start with ename = ‘SCOTT‘
connect by empno = prior mgr;

--从层次查询中删除节点和分支

select level,
  lpad(‘ ‘,2 * level - 1) || ename as "Ename"
  ,job
from emp
where ename != ‘SCOTT‘  --通过where子句来过滤SCOTT用户，但SCOTT的下属ADAMS并没有过滤掉
start with empno = 7839   
connect by prior empno = mgr;

--通过将过滤条件由where 子句的内容移动到connect by prior 子句中过滤掉SCOTT及其下属

select level,
  lpad(‘ ‘,2 * level - 1) || ename as "Ename"
  ,job
from emp
start with empno = 7839
connect by prior empno = mgr and ename != ‘SCOTT‘;

--在层次化查询中增加过滤条件或使用子查询

select level,
  lpad(‘ ‘,2 * level - 1) || ename as "Ename"
  ,job
from emp
where sal > 2500
start with empno = 7839
connect by prior empno = mgr;

select level,
  lpad(‘ ‘,2 * level - 1) || ename as "Ename"
  ,job
from emp
where sal > (select avg(sal) from emp)
start with empno = 7839
connect by prior empno = mgr ;

08.SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

标签：rom   png   等于   sel   start   evel   技术   images   image