PL/SQL基础：阶层查询(1)

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

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

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

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