connect by..start with..

1、格式化層次化查詢的結果：（level偽列，顯示顯示樹中的層次數）

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
start with employee_id=1
connect by prior employee_id=manager_id;

查詢結果：

   level      employee

-----------------------------------------

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

透過檢視格式化之後的結果、員工關係一目瞭然。

 

2、start with句子中使用子查詢

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
start with employee_id=(
select employee_id from more_employees
where first_name='Susan' and last_name='Jones'
)
connect by prior employee_id=manager_id;

查詢結果：

level    employee

-----------------------------

   1     Susan Jones
   2          Jane Brown
   3               Henry Heyson
   2          John Grey
   3               Jean Blue

3、從下向上遍歷樹

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
start with employee_id=(
select employee_id from more_employees
where first_name='Susan' and last_name='Jones'
)
connect by prior manager_id=employee_id;

查詢結果：

1 1     Susan Jones
2 2          James Smith

 

4、子查詢中刪除節點和分支

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
where last_name !='Johnson'
start with employee_id=1
connect by prior employee_id=manager_id;

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

查詢結果：Ron Johnson已清除，但它的下屬Rob Green 、Fred Hobbs仍存在，

可以再connect by prior使用and子句將 將其Ron Johnson下屬從結果中除去：

select level,LPAD(' ',5*level-1) || first_name || ' ' || last_name
as employee
from more_employees
start with employee_id=1
connect by prior employee_id=manager_id and last_name !='Johnson';

 

查詢結果：

1 1     James Smith
2 2          Susan Jones
3 3               Jane Brown
4 4                    Henry Heyson
5 3               John Grey
6 4                    Jean Blue
7 2          Kevin Black
8 3               Keith Long
9 3               Frank Howard
10 3               Doreen Penn