第十一章：层次查询



--层次查询
--创建示例数据库表：
create table myemp as
  select employee_id, last_name, manager_id 
      from hr.employees 
      where rownum<=10order by employee_id;

select  *  from myemp;

--示例1：在hr.myemp表中，找出编号为101的雇员及其
--所有下属(包括直接和间接下属)
select employee_id,last_name,manager_id
  from myemp
  start with employee_id=101
  connect by prior employee_id=manager_id
  order by employee_id;

--示例2：在hr.myemp表中，找出编号为101的雇员及
--其所有上司(包括直接和间接上司)
select employee_id,last_name,manager_id
  from myemp
  start with employee_id=101
  connect by  employee_id= prior manager_id
  order by employee_id;  

--使用伪列level显示表中节点的层次关系
--示例3：在hr.myemp表中，找出编号为101的雇员及其所有下属，
--并显示每个人的等级层次
select level,employee_id,last_name,manager_id
  from myemp
  start with employee_id=101
  connect by prior employee_id=manager_id
  order by employee_id;

--注意，level伪列只能和connectby子句结合使用，否则Oracle会返回错误
--示例4：
--ORA-01788: 此查询块中要求CONNECT BY 子句
select level,p.*
  from ( select employee_id,last_name,manager_id
  from myemp
  start with employee_id=101
  connect by prior employee_id=manager_id
  order by employee_id) p;

--统计表中节点的层数
--示例5：在hr.myemp表中，查询所有雇员总共分为几个等级级别？
select count(distinctlevel)
  from myemp
  start with manager_id isnull
  connect by prior employee_id= manager_id;

--可见，在统计level的时候一定要使用distinct关键字，否则会得到错误的结果

--统计表中各个层次的节点数量
--示例7：在hr.myemp表中，查询每个等级级别的雇员数量
select level,count(level)
  from myemp
  start with manager_id isnull
  connect by prior employee_id=manager_id
  group by level;

--示例8：在hr.myemp表中，查看level=2的所有雇员的信息
--使用WHERE子句来过滤某些节点
select level,employee_id,last_name,manager_id
  from myemp
  where level=2
  start with manager_id isnull
  connect by prior employee_id=manager_id
  ;

--注意， CONNECT BY子句要先于WHERE子句执行
--WHERE条件必须写在STARTWITH前面，否则会报语法错误

--示例11：使用缩进的方式来直观显示节点之间的层次关系
select level,employee_id,
    lpad(' ',2*(level-1))||last_name,
    manager_id
  from myemp
  start with manager_id isnull
  connect by prior employee_id=manager_id
  ;

--在STARTWITH中使用子查询
--针对查询的起始点不容易直接确定的情况
--示例12：查询雇员编号最小的雇员节点及其子节点
select level,employee_id,
    lpad(' ',2*(level-1))||last_name,
    manager_id
  from myemp
  start with employee_id=(selectmin(employee_id) from myemp)
  connect by prior employee_id=manager_id
  ;

--判断节点和节点之间是否具有层次关系
--使用WHERE子句，判断某个节点是否存在于另一个节点的节点树中
--示例13：查询雇员Kochhar是不是雇员Ernst的领导
select employee_id,
    last_name,
    manager_id
  from myemp
  where last_name='Ernst'
  start with last_name='Kochhar'
  connect by prior employee_id=manager_id
  ;

--删除表中的层次数据（子树）
--示例14：雇员Kochhar及其下属雇员全部离职了，从hr.myemp表中删除他们的全部记录
delete from myemp
  where employee_id in (
    select employee_id
      from myemp
      start with last_name='Kochhar'
      connect by prior employee_id= manager_id
  );

rollback;

--层次查询的增强特性
--1、sys_connect_by_path函数:显示每个节点的路径

--示例15：在hr.myemp表中，查询所有雇员的等级级别，
--并在输出中显示每个节点的路径
select level, employee_id,
    lpad(' ',2*(level-1))||sys_connect_by_path(last_name,'=>'),
    manager_id
  from myemp  
  start with manager_id isnull
  connect by prior employee_id=manager_id
  ;

--CONNECT_BY_ISLEAF伪列
--用于判断层次查询结果集中的行是不是叶子节点。
--返回值0表示不是叶子节点，1表示是叶子节点
select level, employee_id,   
    manager_id,connect_by_isleaf
  from myemp  
  start with manager_id isnull
  connect by prior employee_id=manager_id
  ;

--CONNECT_BY_ROOT操作符
--用在列名之前，找出此行的根节点行的相同列名的值
--示例17：
SELECT last_name "Employee",
    CONNECT_BY_ROOT last_name "Manager",
    LEVEL-1"Pathlen",  
    SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM myemp  
   WHERE  LEVEL> 1 
   CONNECT BY PRIOR employee_id= manager_id
   ORDER BY "Employee", "Manager", "Pathlen", "Path";