解决树状关系查询
示例:
QL> select employee_id,last_name,job_id,manager_id from employees 2 start with employee_id=101 3 connect by prior manager_id=employee_id;
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID ----------- ------------------------- ---------- ---------- 101 Kochhar AD_VP 100 100 King AD_PRES
SQL> select last_name || 'report to' || prior last_name "Walk T D" 2 from employees 3 start with last_name='King' 4 connect by prior employee_id=manager_id;
Walk T D ----------------------------------------------------------- Kingreport to Kingreport to Kochharreport toKing Greenbergreport toKochhar Favietreport toGreenberg Chenreport toGreenberg Sciarrareport toGreenberg Urmanreport toGreenberg Poppreport toGreenberg Whalenreport toKochhar Mavrisreport toKochhar
Walk T D ----------------------------------------------------------- Baerreport toKochhar Higginsreport toKochhar
LEVEL和LPAD
示例:
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'#') 2 as org_chart 3 from employees 4 start with first_name='Steven' and last_name='King' 5 connect by prior employee_id=manager_id;
ORG_CHART -------------------------------------------------------------------------------- King ##Kochhar ####Greenberg ######Faviet ######Chen ######Sciarra ######Urman ######Popp ####Whalen ####Mavris ####Baer
Pruning Branches修剪问题
WHERE去掉一个节点,AND去掉整个枝干。
|