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

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

  

层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下:

   SELECT [LEVEL] ,column,expression,...
   FROM table_name
   [WHERE where_clause]
   [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
  
   LEVEL:为伪列,用于表示树的层次
   start_condition：层次化查询的起始条件

 

--使用start with ...connect by prior 从根节点开始遍历

select empno,mgr,ename,job from emp
start with empno = 7839
connect by prior empno = mgr;

 

树型结构遍历过程(通过上面的查询来描述)
　　1).从根节点开始(即where_clause中的条件,如果为非根节点则分根节点作为根节点开始遍历,如上例empno = 7839)
　　2).遍历根节点(得到empno = 7839记录的相关信息)
　　3).判断该节点是否存在由子节点，如果则访问最左侧未被访问的子节点,转到),否则下一步
　　　　如上例中prior_condition为empno = mgr,即子节点的mgr等于父节点的empno,在此时mgr为7839的记录
　　4).当节点为叶节点，则访问完毕,否则,转到)
　　5).返回到该节点的父节点,转到)
 
--伪列level的使用
--注意connect by prior empno = mgr 的理解
--prior表示前一条记录,即下一条返回记录的mgr应当等于前一条记录的empno

　　

 

select level,empno,mgr,ename,job from emp
start with ename = 'KING'
connect by prior empno = mgr
order by level;

 

--获得层次数

select count(distinct level) "Level" from emp
start with ename = 'KING'
connect by prior empno = mgr;

 

 

 

--格式化层次查询结果(使用左填充* level - 1个空格)

col Ename for a30
select level,
  lpad(' ',2 * level - 1) || ename as "Ename",
  job
from emp
start with ename = 'KING'
connect by prior empno = mgr; 

 

 

--从非根节点开始遍历(只需修改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 ;