oracle sql 高级编程学习笔记（二十五)

connect by 递归查询实例演示

1、 connect by 演示

递归查看所有员工之间组织关系

select lpad(' ', level * 2 - 1, ' ') || emp.last_name emp_last_name,
-- level 伪列保存了递归的深度值   level =2 表示 递归了2-1层，即只查询KING id为100的所有直接下属 见下面伪列演示
--这里时通过空格来表示递归深度关系
       emp.first_name emp_first_name,
       emp.employee_id,
       emp.manager_id,
       mgr_last_name
  from (select /*+ inline gather_plan_statistics*/e.last_name, e.first_name, e.employee_id, e.manager_id,
       es.last_name mgr_last_name
          from employees e  
          left outer join employees es
            on es.employee_id = e.manager_id) emp
connect by prior emp.employee_id = emp.manager_id
-- 扩展  prior 在左边表示 查询下级，在右变表示 求上级
 start with emp.manager_id is null
 order siblings by emp.last_nameL;

扩展 ：order siblings by 表示兄弟姐妹之间的排序 只针对树结构结果集，
详见 https://blog.csdn.net/niuhea/article/details/8596307
查询结果如下：

2、level 伪列演示

select   emp.last_name emp_last_name,
       emp.first_name emp_first_name,
       emp.employee_id,
       emp.manager_id,
       mgr_last_name
  from (select /*+ inline gather_plan_statistics*/e.last_name, e.first_name, e.employee_id, e.manager_id,
       es.last_name mgr_last_name
          from employees e  
          left outer join employees es
            on es.employee_id = e.manager_id) emp
            where level =2
connect by prior emp.employee_id = emp.manager_id
 start with emp.manager_id is null
 order siblings by emp.last_name;

因为 start with指定了递归开始是从King开始，而level 空值递归深度，即这样只查出KING的所有直接下级，共14人，
查询结果:

3、扩展演示:查询上级

查看所有206员工的所有上级

select   emp.last_name emp_last_name,
       emp.first_name emp_first_name,
       emp.employee_id,
       emp.manager_id,
       mgr_last_name
  from (select /*+ inline gather_plan_statistics*/e.last_name, e.first_name, e.employee_id, e.manager_id,
       es.last_name mgr_last_name
          from employees e  
          left outer join employees es
            on es.employee_id = e.manager_id) emp
connect by  emp.employee_id = prior emp.manager_id
 start with  emp.employee_id='206'
 order siblings by emp.last_name;

同样这里也可以通过level 来控制查看所有上级还是直接上级，还是上级的上级……