【SQL 学习】层次化查询之CONNECT BY 和 START WITH

SQL> select employee_id ,manager_id ,first_name ,last_name
  2  from emp
  3  start with employee_id =1--- 定义层次化查询的起点
  4  connect by prior employee_id = manager_id;指定父行与子行之间的关系。也就是父节点的employee_id 等于  子节点的manager_id

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
-----------               ----------           ---------------       ----------------
          1                                              James      Smith
          2                         1                   Ron           Johnson
          3                         2                   Fred          Hobbs
          5                         2                   Rob           Green
          4                         1                   Susan      Jones
          6                         4                   Jane         Brown
          9                         6                   Henry        Heyson
          7                         4                   John         Grey                   
          8                         7                   Jean         Blue                      
         10                       1                    Kevin        Black              
         11                     10                    Keith         Long
         12                     10                    Frank        Howard
         13                     10                    Doreen     Penn

已选择13行。
执行计划
----------------------------------------------------------                                                                       
Plan hash value: 1213993571               
                                                                                                                 
------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name | Rows  | Bytes | Cost (%CPU)| Time     |               
------------------------------------------------------------------------------------------------               
|   0 | SELECT STATEMENT                        |      |    13 |   234 |     3   (0)| 00:00:01 |              
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |       |       |            |          |              
|   2 |   TABLE ACCESS FULL                     | EMP  |    13 |   234 |     3   (0)| 00:00:01 |              
------------------------------------------------------------------------------------------------               
Predicate Information (identified by operation id):
---------------------------------------------------               
                                                                                                                                 
   1 - access("MANAGER_ID"=PRIOR "EMPLOYEE_ID")               
       filter("EMPLOYEE_ID"=1)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-673259/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-673259/