分层查询（Hierarachical Queries）（翻译）

翻译自oracle 官网中的Hierarchical Queries
网址：https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm#i2060615
大家可以过去看英文版

如果一张表中存储着分层（树状）数据，那么你就可以使用分层查询语句以分层（树状）顺序来进行查询

分层查询语句的语法：

START WITH:分层（树状）数据中的根节点
CONNECT BY:分层（树状）数据中父节点与子节点的特殊联系

The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP exists in the data. Use this
parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which
rows contain the loop. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information.

• 翻译：当在数据中存在以（connect by loop）为条件查询到结果时，那么关键字（nocycle）(非循环关键字)将会通知oracle数据库从集合中返回这些行。使用参数connect_by_iscycle来查看在循环结果中包含哪些行。

In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example

• 翻译：在层次（树状）查询时，条件中的表达式必须存在用于指代双亲节点（父行/父节点）合适的条件（prior），例如

... PRIOR expr = expr
or
... expr = PRIOR expr

If the CONNECT BY condition is compound, then only one condition
requires the PRIOR operator, although you can have multiple PRIOR
conditions. For example:

如果连接条件（CONNECT BY）是多个的，那么仅需要在连接条件中包含一个前置（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经常用在比较两列的值是否相等的操作中，（该关键字prior可以放在等式的两端）。有了prior，在oracle中就可以使用该列中父节点的值。在CONNECT BY 子句中也可以用除了 = 号之外的其他操作符，不过这些操作符可能导致无限循环的结果，在这种情况下oracle会发现循环超时并返回错误信息。

Both the CONNECT BY condition and the PRIOR expression can take
the form of an uncorrelated subquery. However, the PRIOR expression
cannot refer to a sequence. That is, CURRVAL and NEXTVAL are not valid
PRIOR expressions。

CONNECT BY 条件和PRIOR表达式可以被用在无关子查询中。但是，PRIOR表达式不能存在表示前后顺序的关键词，就是说不能使用比如前一个（CURRVAL）和后一个（NEXTVAL）的表示。

You can further refine a hierarchical query by using the
CONNECT_BY_ROOT operator to qualify a column in the select list. This
operator extends the functionality of the CONNECT BY [PRIOR] condition
of hierarchical queries by returning not only the immediate parent row
but all ancestor rows in the hierarchy.

你可以通过CONNECT_BY_ROOT操作符详细的获取查询结果列表中特定的列，这个操作符扩大的了CONNECT BY [PRIOR]函数的查询结果不仅会返回当前行的双亲节点同时也会返回树状（层状）模型中的祖先节点。

分层查询的图形表示

此处建议回顾一下，深度优先算法

HIERARACHICAL QUERY EXAMPLE
CONNECT BY 例子

The following hierarchical query uses the CONNECT BY clause to define
the relationship between employees and managers:

下面的分层查询通过使用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例子

The next example is similar to the preceding example, but uses the
LEVEL pseudocolumn to show parent and child rows:

相似的例子，不过通过使用伪列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 例子

The next example adds a START WITH clause to specify a root row for the hierarchy and an ORDER BY clause using the SIBLINGS keyword to preserve ordering within the hierarchy:

下面的例子添加了一个START WITH子句来指明一个根节点，同时使用ORDER SLBLINGS 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
...

In the hr.employees table, the employee Steven King is the head of the
company and has no manager. Among his employees is John Russell, who
is the manager of department 80. If we update the employees table to
set Russell as King’s manager, we will create a loop in the data:

属于用户hr的雇员表（hr.employees），Steven King是公司的BOSS，没有上司（管理者），他的雇员中John Russell是80部门的经理。如果我们把雇员表中Steven King的上司设为Russell，然后编写下面循环代码（要报错了！要报错了！要报错了！）

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

The NOCYCLE parameter in the CONNECT BY condition causes Oracle to
return the rows in spite of the loop. The CONNECT_BY_ISCYCLE
pseudocolumn shows you which rows contain the cycle:

在CONNECT BY条件中使用非循环（NOCYCLE）参数使用oracle返回的行出现恶性循环，可以通过伪列CONNECT_BY_ISCYCLE指出你的那个行包含在这个恶性循环中（值为1是恶性循环的原因）。

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;

Employee                   Cycle  LEVEL Path
------------------------- ------ ------ -------------------------
Russell                        1      2 /King/Russell
Tucker                         0      3 /King/Russell/Tucker
Bernstein                      0      3 /King/Russell/Bernstein
Hall                           0      3 /King/Russell/Hall
Olsen                          0      3 /King/Russell/Olsen
Cambrault                      0      3 /King/Russell/Cambrault
Tuvault                        0      3 /King/Russell/Tuvault
Partners                       0      2 /King/Partners
King                           0      3 /King/Partners/King
Sully                          0      3 /King/Partners/Sully
McEwen                         0      3 /King/Partners/McEwen
...

CONNECT_BY_ROOT 例子

The following example returns the last name of each employee in
department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:

下面的例子返回在部门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

The following example uses a GROUP BY clause to return the total salary of each employee in department 110 and all employees below that employee in the hierarchy:
下面的例子使用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

可以学习下——这些我都还没翻译-哈哈-等我翻译过会说明。

LEVEL Pseudocolumn and CONNECT_BY_ISCYCLE Pseudocolumn for a
discussion of how these pseudocolumns operate in a hierarchical query

通过 伪列LEVEL 和CONNECT_BY_ISCYCLE的练习，讨论在分层查询中如何使用伪列操作。

SYS_CONNECT_BY_PATH for information on retrieving the path of column
values from root to node

通过SYS_CONNECT_BY_PATH检索从根节点到子节点（叶子）的路径

order_by_clause for more information on the SIBLINGS keyword of ORDER
BY clauses

ORDER SIBLINGS BY,通过ORDRE BY子句的关键字学习到更多信息。