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: