本文主要是介绍Oracle 层级查询(Hierarchical Queries),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
如果一张表中的数据存在分级(即数据间存在父子关系),利用普通SQL语句显示数据间的层级关系非常复杂,可能需要多次连接才能完整的展示出完成的层级关系,更困难的是你可能不知道数据到底有多少层。而利用Oracle的层级查询,则可以很方便的显示出层级。
一、语法简介
层级关系定义语法如由start with和connect by两个子句构成:
start with … connect by [nocycle] [prior] …
或
connect by [nocycle] [prior] … start with …
- start with …,定义根节点,即层级关系的起点
- connect by …,定义层级关系,即上下级的连接条件
- prior,层级关系中指定父级列
- nocycle,当层级关系出现循环时依然输出结果,和connect_by_iscycle配合使用
相关伪列/函数/排序:
- level,显示当前记录所在的层级,根节点的层级为1,下级为2,依次类推
- sys_connect_by_path,显示指定列的完整层级关系,可以自定义连接符
- connect_by_isleaf,判断当前记录是否叶子节点(没有子孙节点)
- connect_by_iscycle,和nocycle配合使用,判断层级关系是否存在循环
- connect_by_root …,显示当前记录的根节点相关信息
- order siblings by …,按照层级依次排序,即先按层级1排序,再按层级2排序,依次类推
二、应用示例
我们以Oracle Sample Schema中的hr.employees表来演示。这张雇员表中的记录通过2个字段定义上下级关系,employee_id为雇员编号,manager_id为上级的雇员编号,例如King的employee_id为100,他的manager_id是空(没有上级),Kochhar的manager_id是100,代表他的上级是King:
select last_name, employee_id, manager_id from employees;
2.1 基本层级查询
下面的SQL查询每位雇员的层级,同时用通过伪列evel显示出来:
select last_name, employee_id, manager_id,level
from employees
start with employee_id=100 connect by manager_id=prior employee_id;
- start with employee_id=100,定义根节点,这里代表从King开始计算层级
- level 伪列显示当前记录的层级,根节点King的Level为1,Kochhar为King的下级,level为2
- connect by 定义层级关系,这里是通过manager_id和employee_id的关系判断层级
- prior 指示层级关系中谁是父级列,虽然我们从列名的含义可以判断出manager_id对应上级记录的employee_id,但是Oracle并不知道,我们需要用prior关键字指示,即manager_id(本级记录)=prior employee_id(上级记录);
上面的示例,如果倒过来查层级关系,将employee_id为110的雇员(层级为4)作为根节点,那么prior关键字也需要换位置:
select last_name, employee_id, manager_id,level
from employees
start with employee_id=110 connect by prior manager_id=employee_id;
2.2 显示完整层级关系
通过函数sys_connect_by_path可以显示完整的层级路径,该函数有2个参数,列和连接符号。示例中还使用了ltrim函数去除了最左边的连接符:
select last_name, employee_id, manager_id, level,
ltrim(sys_connect_by_path(last_name, ' => '),' => ') Hierarchy
from employees
start with employee_id=101 connect by manager_id=prior employee_id;
2.3 显示是否存在循环
Kochhar对应的上级employee_id为100,如果修改成206,那么层级关系就出现了循环(子孙节点同时也是自己的祖先节点),当出现循环时,普通的层级查询会出现下列报错:
这时可以使用nocycle关键字,指示即使出现循环依然返回结果,并通过connect_by_iscycle伪列显示哪些记录出现了循环:
select last_name, employee_id, manager_id, level, ltrim(sys_connect_by_path(last_name, ' => '),' => ') Hierarchy,
connect_by_iscycle 是否循环
from employees
start with employee_id=101 connect by nocycle manager_id=prior employee_id;
2.4 判断是否为叶子节点
connect_by_isleaf伪列可以显示当前记录是否为叶子节点,如果是叶子节点则返回1,否则返回0。例如用where connect_by_isleaf=1可以过滤出所有的叶子节点:
select last_name, employee_id, manager_id, level, connect_by_isleaf
from employees
-- where connect_by_isleaf =1
start with employee_id=101 connect by manager_id=prior employee_id;
2.5 显示层级的根节点
connect_by_root后面跟上列名,可以显示该列层级的根节点:
select last_name, employee_id, manager_id, level,
ltrim(sys_connect_by_path(last_name, ' => '),' => ') Hierarchy,
connect_by_root last_name Leader
from employees
where level>1
connect by manager_id=prior employee_id;
2.6 按层级排序
在层级查询中,如果要按照层级关系排序,普通的order by语句是无法做到的,此时需要使用order siblings by语句,该语句会按照层级依次对结果进行排序(先按层级1排序,再按层级2排序…):
select last_name, employee_id, manager_id, level, ltrim(sys_connect_by_path(last_name, ' => '),' => ') Hierarchy
from employees
start with employee_id=100 connect by manager_id=prior employee_id
order siblings by last_name;
这篇关于Oracle 层级查询(Hierarchical Queries)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!