CONNECT BY 是 Oracle 專有的層次查詢語法，廣泛用於組織架構、物料 BOM、菜單樹等樹形數據處理。本文系統講解完整語法結構、PRIOR 運算符位置語義、START WITH 根節點定義、ORDER SIBLINGS BY 同級排序、LEVEL/SYS_CONNECT_BY_PATH/CONNECT_BY_ISLEAF 偽列，並給出正反向遍歷對比示例。
SELECT column_list
FROM table_name
[WHERE filter_condition]
[START WITH root_condition]
CONNECT BY [NOCYCLE] PRIOR parent_col = child_col
[ORDER SIBLINGS BY sort_col];
| 子句 | 作用 | 關鍵點 |
|---|---|---|
START WITH | 定義根節點集合 | 可省略 (全表為根)；支持子查詢 START WITH id IN (SELECT ...) |
CONNECT BY | 定義父子關系 | PRIOR 必須放在等式一側：PRIOR parent = child = 父→子 (正向)PRIOR child = parent = 子→父 (反向) |
PRIOR | 指示「上一輪」的列 | 只能用於 CONNECT BY 條件中，不可用於 SELECT/WHERE |
NOCYCLE | 檢測並忽略循環 | 10g+ 支持，配合 CONNECT_BY_ISCYCLE 偽列識別循環節點 |
ORDER SIBLINGS BY | 同級節點排序 | 不影響層次結構，僅調整同父節點下的輸出順序 |
| 偽列 | 類型 | 含義 |
|---|---|---|
LEVEL | NUMBER | 根節點為 1，每層遞增 |
SYS_CONNECT_BY_PATH(col, sep) | VARCHAR2 | 從根到當前節點的路徑拼接 |
CONNECT_BY_ISLEAF | NUMBER | 1=葉子節點，0=非葉子 |
CONNECT_BY_ISCYCLE | NUMBER | 1=發現循環 (需 NOCYCLE)，0=無循環 |
SELECT pid, id, name
FROM t_wf_eng_wfkind
START WITH pid = 0
CONNECT BY PRIOR id = pid
ORDER SIBLINGS BY name;
-- 正向：根→葉
CONNECT BY PRIOR id = pid
-- 反向：葉→根
CONNECT BY PRIOR pid = id
-- 區別在於 PRIOR 放在等式左邊 (父) 還是右邊 (子)，以及 START WITH 指定根或葉
SELECT pid, id, name
FROM v_wf_wfkind_tree
WHERE id IN (
SELECT DISTINCT id
FROM v_wf_wfkind_tree
START WITH id IN ('0','01','0101','0203','0304')
CONNECT BY PRIOR pid = id
);
內層反向遞歸收集祖先鏈路，外層去重並查詢詳情。
WITH RECURSIVE tree_path (id, name, pid, lvl, path) AS (
SELECT id, name, pid, 1, CAST(name AS VARCHAR2(4000))
FROM tbl_test WHERE pid = 0
UNION ALL
SELECT t.id, t.name, t.pid, tp.lvl + 1, tp.path || '>' || t.name
FROM tbl_test t JOIN tree_path tp ON t.pid = tp.id
)
SELECT * FROM tree_path;
跨數據庫兼容性更好，邏輯更直觀，建議新項目採用。
掌握 START WITH 定根、CONNECT BY PRIOR 定向 (父左子右 vs 子左父右)、NOCYCLE 防循環、偽列輔助分析、ORDER SIBLINGS BY 同級排序，即可駕馭 Oracle 層次查詢。理解 PRIOR 位置語義是正反向遍歷的關鍵分水嶺。
