Oracle PL/SQL之递归查询 - CONNECT BY PRIOR

duzz$scott@orcl>SELECT employee_id, last_name, job_id, manager_id 2 FROM employees 3 START WITH employee_id = 101 4 CONNECT BY PRIOR employee_id=manager_id; （PRIOR：先前的，优先的；按照先前的employee_id等于本行的manager_id进行递归查询，从上至下） EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID ----------- ------------------------- ------------------------------ ---------- 101 Kochhar AD_VP 100 108 Greenberg FI_MGR 101 109 Faviet FI_ACCOUNT 108 110 Chen FI_ACCOUNT 108 111 Sciarra FI_ACCOUNT 108 112 Urman FI_ACCOUNT 108 113 Popp FI_ACCOUNT 108 200 Whalen AD_ASST 101 203 Mavris HR_REP 101 204 Baer PR_REP 101 205 Higgins AC_MGR 101 206 Gietz AC_ACCOUNT 205 12 rows selected. Elapsed: 00:00:00.03 duzz$scott@orcl >SELECT employee_id, last_name, job_id, manager_id 2 FROM employees 3 START WITH employee_id = 101 4 CONNECT BY employee_id= PRIOR manager_id; （把先前的manager_id(employee_id = 101)作为当前的employee_id，从下至上） EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID ----------- ------------------------- ------------------------------ ---------- 101 Kochhar AD_VP 100 100 King AD_PRES Elapsed: 00:00:00.00

 

REF:

http://www.9flex.com/archives/295/

http://www.cnblogs.com/ZHF/archive/2008/09/10/1288101.html