Oracle PL/SQL之遞迴查詢 – CONNECT BY PRIOR

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

 

REF:

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

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