SQL-Start with connect by prior用法

參考資料
https://dotblogs.com.tw/jeff-yeh/2009/05/20/8489
http://tomkuo139.blogspot.tw/2009/06/oracle-plsql-treeview.html
https://read01.com/0zGL7o.html

最近與C大研究了Connect by，發現還是有些疑問，開始上網找答案

語法架構

1 2 3 4 5 6

SELECT COL1, COL2,.. .   FROM TABLE [  WHERE CONDITION.. . ]  START WITH 要放在最上層的條件 CONNECT BY [ PRIOR ] CHILD_COLUMN = PARENT_COLUMN [  ORDER [ SIBLINGS ] BY SORT .. . ]

若 prior 有提供，則會查詢出本階與其所有下階資料。
若 prior 無提供，則只查詢出本階資料。
若 siblings 有提供，則會按父子階層排序，再按指定欄位排序。
若 siblings 無提供，則會按指定欄位排序。
Child_Column = Parent_Column，視為 "Child 的父階是哪個"。
查詢會先按 start with 展開 Treeview，再過濾 where 條件。

 

---

 

架設測試環境
CREATE TABLE IT_CONNECT_BY
(
  EMP   VARCHAR2(20),
  NAME  VARCHAR2(20),
  EMAIL VARCHAR2(20),
  BOSS  VARCHAR2(20)
)

測試資料

EMP	NAME	EMAIL	BOSS
A0001	James	james@aaa.com
A0002	Porz	porz@aaa.com	A0001
A0003	Sandy	sandy@aaa.com	A0002
A0004	Grace	grace@aaa.com	A0003

1 2 3 4 5

--沒有 PRIOR 尋找 A0003 的主管 SELECT *   FROM IT_CONNECT_BY CB CONNECT BY CB.EMP = CB.BOSS  START WITH CB.EMP = 'A0003';

結果

EMP	NAME	EMAIL	BOSS
A0003	Sandy	sandy@aaa.com	A0002

1 2 3 4 5 6

--有 PRIOR 尋找 A0003 的主管 --PRIOR 在左邊 SELECT * FROM IT_CONNECT_BY CB CONNECT BY PRIOR CB.BOSS = CB.EMP START WITH CB.EMP = 'A0003';

結果

EMP	NAME	EMAIL	BOSS
A0003	Sandy	sandy@aaa.com	A0002
A0002	Porz	porz@aaa.com	A0001
A0001	James	james@aaa.com

1 2 3 4 5 6

--有 PRIOR 尋找 A0003 的主管 --PRIOR 在右邊 SELECT *   FROM IT_CONNECT_BY CB CONNECT BY CB.BOSS = PRIOR CB.EMP  START WITH CB.EMP = 'A0003';

結果

EMP	NAME	EMAIL	BOSS
A0003	Sandy	sandy@aaa.com	A0002
A0004	Grace	grace@aaa.com	A0003

最後發現 PRIOR 的位置，會決定是由A0003出發向上展或者向下