树形数据结构查询 CONNECT BY PRIOR

在工作中，经常看到对某表进行相关树形结构的查询，比如针对某集团单位的组织机构、对员工上下级关系方面等都常常用到，把用法在这做个简单的记录，也是在博客园第一条笔记:)

 

 SELECT last_name, employee_id, manager_id, LEVEL  
2       FROM employees   
3       START WITH employee_id = 100   
4       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