oracle树形查询
1. WITH AS 示例
with e as (select * from employees), d as (select * from departments) select * from e,d where e.department_id = d.department_id and d.department_id = 10
2. 树形查询
2.1 connect by
兄弟结点排序: order siblings by
PRIOR放在子节点一侧,会从start with开始向下寻找子节点
PRIOR放在父节点一侧,会从start with开始向上寻找父节点
SELECT e.employee_id,e.salary,e.last_name,e.manager_id,level from employees e where level =2 --可直接查询某一层级的数据 start with e.employee_id = 100 connect by e.employee_id =prior e.manager_id order siblings by e.salary asc --兄弟结点间排序
2.2 nocycle
如果树形结构有嵌套循环的话,会报错ORA-01436,需要在CONNECT BY 后关键字NOCYCLE :
CONNECT BY NOCYCLE PRIOR
select last_name "Employee", level, sys_connect_by_path(last_name, '/') "Path" from emp where level <= 3 and department_id = 80 start with last_name = 'King' connect by nocycle prior employee_id = manager_id;
2.3 CONNECT_BY _ISLEAF,查询所有的叶子节点,含有子节点的为0
select sys_connect_by_path(e.last_name, '/') from employees e where connect_by_isleaf = 1 start with e.employee_id = 100 connect by prior e.employee_id = e.manager_id
2.4 CONNECT_BY_ROOT
--如果指定了start_with则获取的是最顶层结点的信息 select e.employee_id, e.last_name, e.manager_id, connect_by_root e.last_name root_last_name, connect_by_root e.hire_date root_hire_date from employees e start with e.employee_id = 100 connect by prior e.employee_id = e.manager_id
--如果为指定start with会依次展示结点的所有的父级结点 with v as (select e.employee_id, e.last_name, e.manager_id, connect_by_root e.last_name root_last_name, connect_by_root e.hire_date root_hire_date from employees e -- start with e.employee_id = 100 connect by prior e.employee_id = e.manager_id) select * from v where v.employee_id = 206