Oracle 階層查詢完全指南：從 CONNECT BY 到字串聚合的樹狀資料處理實戰

核心語法：CONNECT BY PRIOR

SELECT column_list
FROM table_name
START WITH root_condition          -- 根節點條件
CONNECT BY PRIOR parent_key = child_key;  -- 父子關聯

• START WITH：指定根節點（如 super IS NULL 表示頂層主管）。
• CONNECT BY PRIOR：定義遞歸關聯，PRIOR 修飾父層欄位。
• 偽欄位 LEVEL：根節點為 1，每遞歸一層 +1。
• SYS_CONNECT_BY_PATH(col, '/')：從根到當前節點的路徑串聯。

經典範例：員工組織架構

SELECT LPAD(' ', 4*(LEVEL-1)) || ename AS name, job, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

輸出：縮排顯示層級關係，直觀呈現管理鏈。

進階應用：同組別資料字串聚合（需求：同 NO 下 NAME 以 ; 連接）

原始資料

NO  NAME
001 a01
001 a02
001 a03
002 b01
003 c01
003 c02
...

目標結果

NO  RESULT
001 a01;a02;a03
002 b01
003 c01;c02
...

解法：構造虛擬樹 + SYS_CONNECT_BY_PATH + 取最大路徑

1. 產生排序序號（RN）：ROW_NUMBER() OVER (ORDER BY no, name DESC)，同組內按 NAME 降序，確保葉節點在前。
2. 建立父子關聯（RN1）：LEAD(rn) OVER (PARTITION BY no ORDER BY rn)，取同組下一筆 RN 作為父節點。
3. 遞歸串聯路徑：START WITH rn1 IS NULL CONNECT BY rn1 = PRIOR rn，從最後一筆（根）向前遞歸。
4. 分組取最長路徑：MAX(SYS_CONNECT_BY_PATH(name, ';')) 即為完整聚合字串。

完整 SQL

SELECT no, MAX(SYS_CONNECT_BY_PATH(name, ';')) AS result
FROM (
    SELECT no, name, rn, LEAD(rn) OVER (PARTITION BY no ORDER BY rn) AS rn1
    FROM (
        SELECT no, name, ROW_NUMBER() OVER (ORDER BY no, name DESC) AS rn
        FROM a_example2
    )
)
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY no;

12c+ 現代寫法：LISTAGG 一行搞定

SELECT no, LISTAGG(name, ';') WITHIN GROUP (ORDER BY name) AS result
FROM a_example2
GROUP BY no;

優勢：語法簡潔、效能優於遞歸、支援 ON OVERFLOW TRUNCATE 防止超長字串報錯（ORA-01489）。

效能與避坑指南

總結

Oracle 階層查詢以 CONNECT BY 為核心，配合 SYS_CONNECT_BY_PATH 可解決字串聚合、路徑顯示等進階需求。12c 起 LISTAGG 成為聚合首選，但理解虛擬樹構造思維對維護舊系統、跨版本相容仍具高價值。