oracle 查询总结,SQL:Oracle层次查询总结 connect by

oracle 层次查询 语法：

SELECT… FROM

[WHEREcondition]

–过滤某些节点

[ STARTWITH[nocycle]

start_condition]–定义查询的起点, 可以使用子查询

CONNECTBY[[nocycle]PRIORCOLUMN1 = COLUMN2 [AND

…]];   –定义父子关系

order [ sibilings ] by …

例1：

找出101雇员, 及其全下属/上司select *

frommyemp

startwith employee_id = 101connectby prior employee_id = manager_id; --找下属--connect by employee_id = prior manager_id; --找上司

prior在等号哪边,表示哪边是"我的"

找下属:  "我的"employee_id = "别人的"manager_id –> 找出我的下属  (向下查询)

找上司: employee_id = prior manager_id 别人的工号 = 我的经理编号 –> 别人是我的经理 & 别人是我经理的经理 –> 我的上司们

注意，level伪列只能和connect by子句结合使用，否则Oracle会返回错误 ORA-01788: 此查询块中要求 CONNECT BY 子句

例2:

统计树形的层数SELECT COUNT(DISTINCT LEVEL)FROMEMPLOYEES

STARTWITH MANAGER_ID IS NULLCONNECTBY PRIOR EMPLOYEE_ID = MANAGER_ID;

例3:

统计树的节点数量 ,例如, 查询每个级别的雇员数量

select count(level) --在统计树种节点的数量时, 一定不能加distinct!

fromemployees

startwith manager_id is nullconnectby prior employee_id =manager_idgroup by level;

例4:

删除子树delete frommyempwhere employee_id in ( selectemployee_idfrommyemp

startwith last_name = 'Kochhar'connectby prior employee_id = manager_id);

过滤某些结果集

场景1:使用 where 过滤某些节点 , 注意不是过滤分支!

例1:

查看level=2的所有雇员的信息select level, employee_id, last_name, manager_idfromemployeeswhere level = 2 --注意where子句出现的位置

start with manager_id is nullconnectby prior employee_id = manager_id;

注意：where子句比connect by后执行。

即先用connect by生成一颗树, 然后再用where来砍树, 并不是where在前面就先执行它

例2:

查询Mavris是不是Kochhar的雇员SELECT *

FROMemployeesWHERE last_name = 'Mavris'STARTWITH last_name = 'Kochhar' --Kochhar的所有雇员

CONNECT BY PRIOR employee_id = manager_id;

场景2: 使用 connect by  … and … 过滤某些分支例1 查询Raphaely及其的所有下属select *

fromemployees

startwith last_name = 'Raphaely'connectby prior employee_id = manager_id;

例2 查询除了Raphaely和他下属的所有员工select *

fromemployees

startwith manager_id is nullconnectby prior employee_id =manager_idand last_name <> 'Raphaely';

格式化查询  lpad(‘-‘, 3 * (level – 1), ‘-‘)

例：使用三个横杠作为缩进格式化查询select *

fromemployees

startwith manager_id is nullconnectby prior employee_id =manager_idand last_name <> 'Raphaely';

SYS_CONNECT_BY_PATH() 函数 ☆

作用:

将父节点到当前节点的路径按照指定的模式展现出来

格式:

sys_connect_by_path(,)

CONNECT_BY_ISLEAF 伪列

作用:

判断层次查询结果集中的行是不是叶子节点

返回值:

0表示不是叶子节点，

1表示是叶子节点

例：

CONNECT_BY_ROOT 字段x -> 找到该节点最dine顶端节点的字段x

用在列名之前，找出此行的根节点行的相同列名的值

不是一直找到"根", 而是一直找到当前便利的分支的

selectlast_name "Employee",

connect_by_root last_name "Manager",

sys_connect_by_path(last_name,'->') "Path"fromhr.employeeswhere level > 1

--start with 加不加??

connect by prior employee_id =manager_idorder bylast_name, length("Path");

思考? 为什么不能家start with ?加了会有什么效果

不加start with , 则每个节点都遍历一次 , connect_by_root 找到顶端的经理人会不同

而加了start with manager_id is null 则从树的根节点 King 开始遍历, 从而connect_by_root每个人的顶端的经理都是King

10g新特性 采用sibilings排序

作用:

因为使用order by排序会破坏层次，所以在oracle10g中，增加了siblings关键字的排序给叶子节点的关键字排序

语法：

order siblings by  asc|desc ;

它会保护层次，并且在每个等级中按expre排序

注意:

order siblings by 必须紧跟着connect by

所以不能再用order by 了

例子：

select t.employee_id,t.manager_id,t.first_name,t.salary,sys_connect_by_path(t.first_name, '->'),level

fromhr.employees t

startwith manager_id is nullconnectby prior employee_id =manager_idorder by salary desc;

最后的结果是严格按照salary排序的,这样把层级关系都打乱了

采用sibilings排序:

selectt.employee_id,

t.manager_id,

t.first_name,

t.salary,

sys_connect_by_path(t.first_name,'->'),level

fromhr.employees t

startwith manager_id is nullconnectby prior employee_id =manager_idorder siblings by salary desc;

结果的树结构没有被打乱,且没层级的sibilings都是按照salary排序的, 屌屌的~

点赞 (0)赏分享 (0)