第9章 SQL查询与子查询：分层查询

原文地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

分层查询

如果一个表中包含分层数据，则可以使用分层查询子句按照分层顺序返回数据行：

hierachical_query_clause::=

START WITH指定了分层的根节点行。

CONNECT BY指定了父节点行与子节点行之间的关系。

• NOCYCLE参数指示数据存在CONNECT BY 循环时仍然返回数据行。将该参数与CONNECT_BY_ISCYCLE伪列一起使用可以查看包含循环的行。参考“第三章 伪列：分层查询伪列”。

• 分层查询中condition中的某个表达式必须使用PRIOR操作符引用父节点行。例如：... PRIOR expr = expr or ... expr = PRIOR expr。如果CONNECT BY condition是一个组合条件，只有一个条件需要PRIOR操作符，也可以使用多个PRIOR条件。例如：CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...，或者：CONNECT BY PRIOR employee_id = manager_id and PRIOR account_mgr_id = customer_id ...。

PRIOR是一元运算符，与一元+和一元-具有相同的优先级。它为当前行的父节点行计算后面的表达式，PRIOR employee_id = manager_id表示父节点行的雇员ID为当前行的经理ID。

PRIOR最通常是用于等号运算符比较的列值。理论上也可以使用其他运算符；但是，可能导致无限循环。Oracle检测到循环时会返回一个错误。

CONNECT BY条件和PRIOR表达式都可以使用非关联子查询。但是，PRIOR不能引用序列。也就是说，CURRVAL和NEXTVAL是无效的PRIOR表达式。

可以使用CONNECT_BY_ROOT运算符进一步精确定位一个列。该运算符扩展了CONNECT BY [PRIOR]的功能，不仅仅返回直接父节点行，而是所有的祖先节点行。参考“第4章 运算符：分层查询运算符”。

Oracle安装以下方式处理分层查询：

• 如果存在连接，无论是在FROM子句或者WHERE子句谓词中，首先进行连接处理。

• 处理CONNECT BY条件。

• 处理其他的WHERE子句谓词。

Oracle按照以下步骤，使用这些处理的结果信息进行分层：

1. 选择分层的根节点行，即使用START WITH条件指定的行。

2. 选择每一个根节点行的子节点行。子节点行必须满足各自根节点行的CONNECT BY条件。

3. 选择依次继承的子节点行。

4. 如果查询包含一个非连接的WHERE子句，使用该条件排除不满足的行。该条件单个应用与每一行，而不是删除不满足条件的行的所有子节点行。

5. 安照图9-1的顺序返回行。

图9-1 分层查询

为了查询父节点行的子节点，Oracle使用PRIOR表达式计算父节点行，使用另一边的表达式计算表中的每一行。条件为真的行就是它的子节点行。CONNECT By可以包含其他进一步过滤结果的条件，但是不能包含子查询。

如果CONNECT BY条件导致循环结构，Oracle返回一个错误。循环就是某一行既是另外一行的父节点行（祖父节点或者直接祖先节点），又是它的子节点行（孙子节点或者直接子孙节点）。

注释：不要在分层查询中指定ORDER BY或者GROUP BY，因为这会破坏结果的分层顺序。如果想要对兄弟节点行进行排序，使用ORDER SIBLINGS BY子句。

分层查询示例

CONNECT BY示例

以下分层查询使用CONNECT BY子句定义雇员与经理之间的关系：

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

...

LEVEL示例

以下示例与上一个类似，但使用了LEVEL伪列显示父节点行与子节点行层级：

SELECT employee_id, last_name, manager_id, LEVEL

   FROM employees

   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL

----------- ------------------------- ---------- ----------

        101 Kochhar                          100          1

        108 Greenberg                        101          2

        109 Faviet                           108          3

        110 Chen                             108          3

        111 Sciarra                          108          3

        112 Urman                            108          3

        113 Popp                             108          3

...

START WITH示例

接下来的示例增加了一个START WITH子句限制了根节点行，以及带SIBLINGS关键字的ORDER BY子句对分层结果进行排序：

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

...

在表hr.employees中，雇员Steven King是公司的领导并且他没有经理。John Russell是他的雇员，同时是部门80的经理。如果更新表，将Russell设置为King的经理，将会创建一个循环：

UPDATE employees SET manager_id = 145

   WHERE employee_id = 100;

SELECT last_name "Employee",

   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"

   FROM employees

   WHERE level <= 3 AND department_id = 80

   START WITH last_name = 'King'

   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;

  2    3    4    5    6    7  ERROR:

ORA-01436: CONNECT BY loop in user data

CONNECT_BY_ROOT示例

以下示例返回部门110的每一个雇员的姓氏、雇员的经理、经理与雇员之间的级别以及两者之间的路径：

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;

Employee        Manager         Pathlen Path

--------------- ------------ ---------- -----------------------------------

Higgins         Kochhar               1 /Kochhar/Higgins

Gietz           Kochhar               2 /Kochhar/Higgins/Gietz

Gietz           Higgins               1 /Higgins/Gietz

Higgins         King                  2 /King/Kochhar/Higgins

Gietz           King                  3 /King/Kochhar/Higgins/Gietz

以下示例使用GROUP BY子句返回部门110的每一个雇员，以及该雇员管理的所有雇员的总薪水：

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;

NAME                      Total_Salary

------------------------- ------------

Gietz                             8300

Higgins                          20300

King                             20300

Kochhar                          20300

hierarchical_query_clause.gif

sqlrf002.gif

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24945919/viewspace-748595/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24945919/viewspace-748595/