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