0%

PLSQL-分层查询

语法

Hierarchical Queries

  • 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

参考链接

  1. Hierarchical Queries