第十一章：层次查询

-
 -层次查询
 
-
 -创建示例数据库表：
 
create 
  table myemp 
 as
 
   
 select employee_id, last_name, manager_id 
 
       
 from hr.employees 
 
       
 where rownum
 <
 =
 10 
 order 
  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伪列只能和
 connect 
 by子句结合使用，否则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(
 distinct 
 level)
 
   
 from myemp
 
   
 start 
  with manager_id 
 is 
 null
 
   
 connect 
  by 
 prior employee_id 
 = manager_id;
 

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

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

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

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

-
 -在
 START 
 WITH中使用子查询
 
-
 -针对查询的起始点不容易直接确定的情况
 
-
 -示例
 12：查询雇员编号最小的雇员节点及其子节点
 
select 
  level,employee_id,
 
     lpad(
 ' ',
 2
 *(
 level
 -
 1))||last_name,
 
     manager_id
 
   
 from myemp
 
   
 start 
  with employee_id
 =(
 select 
 min(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 
 is 
 null
 
   
 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 
 is 
 null
 
   
 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";