oracle 层次查询
语法：
SELECT ... FROM
[WHERE condition] --过滤某些节点
[ START WITH [nocycle] start_condition] --定义查询的起点, 可以使用子查询
CONNECT BY [[nocycle] PRIOR COLUMN1 = COLUMN2 [AND ...]]; --定义父子关系
order [ sibilings ] by ...
例1：
找出101雇员, 及其全下属/上司
select * from myemp start with employee_id = 101connect by 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) FROM EMPLOYEES START WITH MANAGER_ID IS NULLCONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;
例3:
统计树的 节点数量 , 例如, 查询每个级别的雇员数量
select count(level) --在统计树种节点的数量时, 一定不能加distinct! from employees start with manager_id is nullconnect by prior employee_id = manager_id group by level;
例4:
删除子树
delete from myemp where employee_id in ( select employee_id from myemp start with last_name = 'Kochhar' connect by prior employee_id = manager_id);
过滤某些结果集
场景1:使用 where 过滤某些节点 , 注意不是过滤分支!
例1:
查看level=2的所有雇员的信息
select level, employee_id, last_name, manager_id from employees where level = 2 --注意where子句出现的位置 start with manager_id is nullconnect by prior employee_id = manager_id;
注意：where子句比connect by后执行。
即先用connect by生成一颗树, 然后再用where来砍树, 并不是where在前面就先执行它
例2:
查询Mavris是不是Kochhar的雇员
场景2: 使用 connect by ... and ... 过滤某些分支
例1 查询Raphaely及其的所有下属
select * from employees start with last_name = 'Raphaely'connect by prior employee_id = manager_id;
例2 查询除了Raphaely和他下属的所有员工
select * from employees start with manager_id is nullconnect by prior employee_id = manager_id and last_name <> 'Raphaely';
格式化查询 lpad('-', 3 * (level - 1), '-')
例：使用三个横杠作为缩进格式化查询
select * from employees start with manager_id is nullconnect by prior employee_id = manager_id and last_name <> 'Raphaely';
SYS_CONNECT_BY_PATH() 函数 ☆
作用:
将父节点到当前节点的路径按照指定的模式展现出来
格式:
sys_connect_by_path(<列明>,<连接串>)
CONNECT_BY_ISLEAF 伪列
作用:
判断层次查询结果集中的行是不是叶子节点
返回值:
0表示不是叶子节点，
1表示是叶子节点
例：
CONNECT_BY_ROOT 字段x -> 找到该节点最顶端节点的字段x
用在列名之前，找出此行的根节点行的相同列名的值
不是一直找到"根", 而是一直找到当前便利的分支的
select last_name "Employee", connect_by_root last_name "Manager", sys_connect_by_path(last_name, ' -> ') "Path" from hr.employeeswhere level > 1-- start with 加不加??connect by prior employee_id = manager_idorder by last_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 <expre> 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 from hr.employees t start with manager_id is nullconnect by prior employee_id = manager_id order by salary desc;
最后的结果是严格按照salary排序的,这样把层级关系都打乱了
采用sibilings排序:
select t.employee_id, t.manager_id, t.first_name, t.salary, sys_connect_by_path(t.first_name, '->'), level from hr.employees t start with manager_id is nullconnect by prior employee_id = manager_id order siblings by salary desc;
结果的树结构没有被打乱,且没层级的sibilings都是按照salary排序的, 屌屌的~