第十一章：层次查询



- -层次查询
- -创建示例数据库表：
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";