SQL层级查询

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

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. . .

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

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        200 Whalen                           101          2        203 Mavris                           101          2        204 Baer                             101          2        205 Higgins                          101          2        206 Gietz                            205          3        102 De Haan                          100          1...

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

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                     1Cambrault                         148        100          2Bates                             172        148          3Bloom                             169        148          3Fox                               170        148          3Kumar                             173        148          3Ozer                              168        148          3Smith                             171        148          3De Haan                           102        100          2Hunold                            103        102          3Austin                            105        103          4Ernst                             104        103          4Lorentz                           107        103          4Pataballa                         106        103          4Errazuriz                         147        100          2Ande                              166        147          3Banda                             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 you update theemployees table to set Russell as King's manager, you create a loop in the data:

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;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. TheCONNECT_BY_ISCYCLE pseudocolumn shows you which rows contain the cycle:

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/AbelAnde                               0          3 /King/Errazuriz/AndeBanda                              0          3 /King/Errazuriz/BandaBates                              0          3 /King/Cambrault/BatesBernstein                          0          3 /King/Russell/BernsteinBloom                              0          3 /King/Cambrault/BloomCambrault                          0          2 /King/CambraultCambrault                          0          3 /King/Russell/CambraultDoran                              0          3 /King/Partners/DoranErrazuriz                          0          2 /King/ErrazurizFox                                0          3 /King/Cambrault/Fox...

CONNECT_BY_ROOT ExamplesThe 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:

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/GietzGietz           King                     3 /King/Kochhar/Higgins/GietzGietz           Kochhar                  2 /Kochhar/Higgins/GietzHiggins         King                     2 /King/Kochhar/HigginsHiggins         Kochhar                  1 /Kochhar/Higgins

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:

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                             8300Higgins                          20300King                             20300Kochhar                          20300