-
-层次查询
- -创建示例数据库表:
create table myemp as
select employee_id, last_name, manager_id
from hr.employees
where rownum < = 10 order by employee_id;
select * from myemp;
- -示例 1:在hr.myemp表中,找出编号为 101的雇员及其
- -所有下属(包括直接和间接下属)
select employee_id,last_name,manager_id
from myemp
start with employee_id = 101
connect by prior employee_id =manager_id
order by employee_id;
- -示例 2:在hr.myemp表中,找出编号为 101的雇员及
- -其所有上司(包括直接和间接上司)
select employee_id,last_name,manager_id
from myemp
start with employee_id = 101
connect by employee_id = prior manager_id
order by employee_id;
- -使用伪列 level显示表中节点的层次关系
- -示例 3:在hr.myemp表中,找出编号为 101的雇员及其所有下属,
- -并显示每个人的等级层次
select level,employee_id,last_name,manager_id
from myemp
start with employee_id = 101
connect by prior employee_id =manager_id
order by employee_id;
- -注意, level伪列只能和 connect by子句结合使用,否则Oracle会返回错误
- -示例 4:
- -ORA - 01788: 此查询块中要求 CONNECT BY 子句
select level,p. *
from ( select employee_id,last_name,manager_id
from myemp
start with employee_id = 101
connect by prior employee_id =manager_id
order by employee_id) p;
- -统计表中节点的层数
- -示例 5:在hr.myemp表中,查询所有雇员总共分为几个等级级别?
select count( distinct level)
from myemp
start with manager_id is null
connect by prior employee_id = manager_id;
- -可见,在统计 level的时候一定要使用 distinct关键字,否则会得到错误的结果
- -统计表中各个层次的节点数量
- -示例 7:在hr.myemp表中,查询每个等级级别的雇员数量
select level, count( level)
from myemp
start with manager_id is null
connect by prior employee_id =manager_id
group by level;
- -示例 8:在hr.myemp表中,查看 level = 2的所有雇员的信息
- -使用 WHERE子句来过滤某些节点
select level,employee_id,last_name,manager_id
from myemp
where level = 2
start with manager_id is null
connect by prior employee_id =manager_id
;
- -注意, CONNECT BY子句要先于 WHERE子句执行
- - WHERE条件必须写在 START WITH前面,否则会报语法错误
- -示例 11:使用缩进的方式来直观显示节点之间的层次关系
select level,employee_id,
lpad( ' ', 2 *( level - 1))||last_name,
manager_id
from myemp
start with manager_id is null
connect by prior employee_id =manager_id
;
- -在 START WITH中使用子查询
- -针对查询的起始点不容易直接确定的情况
- -示例 12:查询雇员编号最小的雇员节点及其子节点
select level,employee_id,
lpad( ' ', 2 *( level - 1))||last_name,
manager_id
from myemp
start with employee_id =( select min(employee_id) from myemp)
connect by prior employee_id =manager_id
;
- -判断节点和节点之间是否具有层次关系
- -使用 WHERE子句,判断某个节点是否存在于另一个节点的节点树中
- -示例 13:查询雇员Kochhar是不是雇员Ernst的领导
select employee_id,
last_name,
manager_id
from myemp
where last_name = 'Ernst'
start with last_name = 'Kochhar'
connect by prior employee_id =manager_id
;
- -删除表中的层次数据(子树)
- -示例 14:雇员Kochhar及其下属雇员全部离职了,从hr.myemp表中删除他们的全部记录
delete from myemp
where employee_id in (
select employee_id
from myemp
start with last_name = 'Kochhar'
connect by prior employee_id = manager_id
);
rollback;
- -层次查询的增强特性
- - 1、sys_connect_by_path函数:显示每个节点的路径
- -示例 15:在hr.myemp表中,查询所有雇员的等级级别,
- -并在输出中显示每个节点的路径
select level, employee_id,
lpad( ' ', 2 *( level - 1))||sys_connect_by_path(last_name, '=>'),
manager_id
from myemp
start with manager_id is null
connect by prior employee_id =manager_id
;
- -CONNECT_BY_ISLEAF伪列
- -用于判断层次查询结果集中的行是不是叶子节点。
- -返回值 0表示不是叶子节点, 1表示是叶子节点
select level, employee_id,
manager_id,connect_by_isleaf
from myemp
start with manager_id is null
connect by prior employee_id =manager_id
;
- -CONNECT_BY_ROOT操作符
- -用在列名之前,找出此行的根节点行的相同列名的值
- -示例 17:
SELECT last_name "Employee",
CONNECT_BY_ROOT last_name "Manager",
LEVEL - 1 "Pathlen",
SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM myemp
WHERE LEVEL > 1
CONNECT BY PRIOR employee_id = manager_id
ORDER BY "Employee", "Manager", "Pathlen", "Path";
- -创建示例数据库表:
create table myemp as
select employee_id, last_name, manager_id
from hr.employees
where rownum < = 10 order by employee_id;
select * from myemp;
- -示例 1:在hr.myemp表中,找出编号为 101的雇员及其
- -所有下属(包括直接和间接下属)
select employee_id,last_name,manager_id
from myemp
start with employee_id = 101
connect by prior employee_id =manager_id
order by employee_id;
- -示例 2:在hr.myemp表中,找出编号为 101的雇员及
- -其所有上司(包括直接和间接上司)
select employee_id,last_name,manager_id
from myemp
start with employee_id = 101
connect by employee_id = prior manager_id
order by employee_id;
- -使用伪列 level显示表中节点的层次关系
- -示例 3:在hr.myemp表中,找出编号为 101的雇员及其所有下属,
- -并显示每个人的等级层次
select level,employee_id,last_name,manager_id
from myemp
start with employee_id = 101
connect by prior employee_id =manager_id
order by employee_id;
- -注意, level伪列只能和 connect by子句结合使用,否则Oracle会返回错误
- -示例 4:
- -ORA - 01788: 此查询块中要求 CONNECT BY 子句
select level,p. *
from ( select employee_id,last_name,manager_id
from myemp
start with employee_id = 101
connect by prior employee_id =manager_id
order by employee_id) p;
- -统计表中节点的层数
- -示例 5:在hr.myemp表中,查询所有雇员总共分为几个等级级别?
select count( distinct level)
from myemp
start with manager_id is null
connect by prior employee_id = manager_id;
- -可见,在统计 level的时候一定要使用 distinct关键字,否则会得到错误的结果
- -统计表中各个层次的节点数量
- -示例 7:在hr.myemp表中,查询每个等级级别的雇员数量
select level, count( level)
from myemp
start with manager_id is null
connect by prior employee_id =manager_id
group by level;
- -示例 8:在hr.myemp表中,查看 level = 2的所有雇员的信息
- -使用 WHERE子句来过滤某些节点
select level,employee_id,last_name,manager_id
from myemp
where level = 2
start with manager_id is null
connect by prior employee_id =manager_id
;
- -注意, CONNECT BY子句要先于 WHERE子句执行
- - WHERE条件必须写在 START WITH前面,否则会报语法错误
- -示例 11:使用缩进的方式来直观显示节点之间的层次关系
select level,employee_id,
lpad( ' ', 2 *( level - 1))||last_name,
manager_id
from myemp
start with manager_id is null
connect by prior employee_id =manager_id
;
- -在 START WITH中使用子查询
- -针对查询的起始点不容易直接确定的情况
- -示例 12:查询雇员编号最小的雇员节点及其子节点
select level,employee_id,
lpad( ' ', 2 *( level - 1))||last_name,
manager_id
from myemp
start with employee_id =( select min(employee_id) from myemp)
connect by prior employee_id =manager_id
;
- -判断节点和节点之间是否具有层次关系
- -使用 WHERE子句,判断某个节点是否存在于另一个节点的节点树中
- -示例 13:查询雇员Kochhar是不是雇员Ernst的领导
select employee_id,
last_name,
manager_id
from myemp
where last_name = 'Ernst'
start with last_name = 'Kochhar'
connect by prior employee_id =manager_id
;
- -删除表中的层次数据(子树)
- -示例 14:雇员Kochhar及其下属雇员全部离职了,从hr.myemp表中删除他们的全部记录
delete from myemp
where employee_id in (
select employee_id
from myemp
start with last_name = 'Kochhar'
connect by prior employee_id = manager_id
);
rollback;
- -层次查询的增强特性
- - 1、sys_connect_by_path函数:显示每个节点的路径
- -示例 15:在hr.myemp表中,查询所有雇员的等级级别,
- -并在输出中显示每个节点的路径
select level, employee_id,
lpad( ' ', 2 *( level - 1))||sys_connect_by_path(last_name, '=>'),
manager_id
from myemp
start with manager_id is null
connect by prior employee_id =manager_id
;
- -CONNECT_BY_ISLEAF伪列
- -用于判断层次查询结果集中的行是不是叶子节点。
- -返回值 0表示不是叶子节点, 1表示是叶子节点
select level, employee_id,
manager_id,connect_by_isleaf
from myemp
start with manager_id is null
connect by prior employee_id =manager_id
;
- -CONNECT_BY_ROOT操作符
- -用在列名之前,找出此行的根节点行的相同列名的值
- -示例 17:
SELECT last_name "Employee",
CONNECT_BY_ROOT last_name "Manager",
LEVEL - 1 "Pathlen",
SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM myemp
WHERE LEVEL > 1
CONNECT BY PRIOR employee_id = manager_id
ORDER BY "Employee", "Manager", "Pathlen", "Path";