语法
- START WITH：指定层次结构中的根行
- CONNECT BY：指定父级和子级之间的关系。
-
- NOCYCLE参数指示Oracle数据库查询中返回行，即使CONNECT BY 数据中存在循环也是如此。将此参数与CONNECT_BY_ISCYCLE伪列一起使用可以参看哪些行包括循环
-
- 在分层查询中，condition必须使用PRIOR运算符限定一个表达式以引用父行。PRIOR和+或-的优先级一致。PRIOR表达式不能引用序列
可以使用CONNECT_BY_ROOT运算符进一步优化分层查询，以限定列表中的列。此运算符返回层次结构中顶级节点来扩展分层查询的CONNECT BY [PRIOR]的功能
示例
查询员工与管理员的关系
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101
203 Mavris 101
204 Baer 101
|
使用START WITH指定员工，并通过ORDER SIBLINGS BY子句保留分层结构的排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;
LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL
------------------------- ----------- ---------- ----------
King 100 1
Cambrault 148 100 2
Bates 172 148 3
Bloom 169 148 3
Fox 170 148 3
Kumar 173 148 3
Ozer 168 148 3
Smith 171 148 3
De Haan 102 100 2
Hunold 103 102 3
Austin 105 103 4
Ernst 104 103 4
Lorentz 107 103 4
Pataballa 106 103 4
Errazuriz 147 100 2
Ande 166 147 3
Banda 167 147 3
|
NOCYCLE和CONNECT_BY_ISCYCLE结合使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4
ORDER BY "Employee", "Cycle", LEVEL, "Path";
Employee Cycle LEVEL Path
------------------------- ---------- ---------- -------------------------
Abel 0 3 /King/Zlotkey/Abel
Ande 0 3 /King/Errazuriz/Ande
Banda 0 3 /King/Errazuriz/Banda
Bates 0 3 /King/Cambrault/Bates
Bernstein 0 3 /King/Russell/Bernstein
Bloom 0 3 /King/Cambrault/Bloom
Cambrault 0 2 /King/Cambrault
Cambrault 0 3 /King/Russell/Cambrault
Doran 0 3 /King/Partners/Doran
Errazuriz 0 2 /King/Errazuriz
Fox 0 3 /King/Cambrault/Fox
|
查询部门110中每个员工的姓氏，最高级别的经理名字，经理与员工之间的LEVEL及访问路径
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL > 1 and department_id = 110
CONNECT BY PRIOR employee_id = manager_id
ORDER BY "Employee", "Manager", "Pathlen", "Path";
Employee Manager Pathlen Path
--------------- --------------- ---------- ------------------------------
Gietz Higgins 1 /Higgins/Gietz
Gietz King 3 /King/Kochhar/Higgins/Gietz
Gietz Kochhar 2 /Kochhar/Higgins/Gietz
Higgins King 2 /King/Kochhar/Higgins
Higgins Kochhar 1 /Kochhar/Higgins
|
查询部门110中每个员工及该员工之上所有员工的总工资
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT name, SUM(salary) "Total_Salary" FROM (
SELECT CONNECT_BY_ROOT last_name as name, Salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name
ORDER BY name, "Total_Salary";
NAME Total_Salary
------------------------- ------------
Gietz 8300
Higgins 20300
King 20300
Kochhar 20300
|
参考链接
- Hierarchical Queries