树形数据结构查询 CONNECT BY PRIOR

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

 

1  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

转载于:https://www.cnblogs.com/sunshinewu85/archive/2011/07/16/2108326.html