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

喜欢ヅ旅行 2022-05-10 07:54 400阅读 0赞

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][https_blog.csdn.net_niuhea_article_details_8596307]  
查询结果如下：  
![在这里插入图片描述][70]

## 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人，  
查询结果:  
![在这里插入图片描述][70 1]

## 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 来控制查看所有上级还是直接上级，还是上级的上级……

![在这里插入图片描述][70 2]

[https_blog.csdn.net_niuhea_article_details_8596307]: https://blog.csdn.net/niuhea/article/details/8596307
[70]: /images/20220510/051f6f637af14b1fa9524b1a0c5cf7f5.png
[70 1]: /images/20220510/939810a1395d4c1186931dc8b0c3a99c.png
[70 2]: /images/20220510/1b3fe560d46c42c3922f4cfbb8189907.png