SYS_CONNECT_BY_PATH 原設計用於層次查詢構建路徑字符串（如員工管理鏈），需配合 CONNECT BY 使用。本文展示如何將其「借用」於**無層次關系的普通結果集行拼接**，實現類似 LISTAGG（11gR2+）或 WM_CONCAT 的效果。
CREATE TABLE test_sysconnectbypath (name VARCHAR2(20));
INSERT INTO test_sysconnectbypath VALUES ('深圳');
INSERT INTO test_sysconnectbypath VALUES ('武漢');
INSERT INTO test_sysconnectbypath VALUES ('上海');
INSERT INTO test_sysconnectbypath VALUES ('北京');
INSERT INTO test_sysconnectbypath VALUES ('天津');
INSERT INTO test_sysconnectbypath VALUES ('新加坡');
COMMIT;ROWNUM 生成唯一序號 idLAG(id) OVER (ORDER BY id) 獲取上一行 id 作為 pid（父節點）pid IS NULL 作為根節點WITH temp AS (
SELECT name FROM test_sysconnectbypath
),
tree AS (
SELECT t.name,
rn AS id,
LAG(rn) OVER (ORDER BY rn) AS pid
FROM (
SELECT temp.*, ROWNUM rn FROM temp
) t
)
SELECT SYS_CONNECT_BY_PATH(name, ',') AS path
FROM tree
START WITH pid IS NULL
CONNECT BY PRIOR id = pid;| 步驟 | 中間結果 |
|---|---|
| 1. 基礎查詢 + ROWNUM | NAME RN 深圳 1 武漢 2 上海 3 北京 4 天津 5 新加坡 6 |
| 2. LAG 生成 PID | NAME ID PID 深圳 1 (null) 武漢 2 1 上海 3 2 北京 4 3 天津 5 4 新加坡 6 5 |
| 3. CONNECT BY 遞歸 | 按 PRIOR id = pid 從根節點向下遍歷 |
| 4. SYS_CONNECT_BY_PATH 拼接 | 輸出路徑字符串 |
PATH
--------------------------------------------------
,深圳
,深圳,武漢
,深圳,武漢,上海
,深圳,武漢,上海,北京
,深圳,武漢,上海,北京,天津
,深圳,武漢,上海,北京,天津,新加坡取最後一行並去除首個分隔符即得最終拼接結果：
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(name, ',')), ',') AS result
FROM tree
START WITH pid IS NULL
CONNECT BY PRIOR id = pid;
RESULT
--------------------------------------------------
深圳,武漢,上海,北京,天津,新加坡| 優點 | 缺點 |
|---|---|
| 10g/11g 早期版本可用，無需 LISTAGG | 需構造虛擬樹，邏輯較繞 |
| 利用成熟的 CONNECT BY 機制，穩定可靠 | 性能遜於 LISTAGG（需排序、遞歸） |
| 可控制排序（ORDER BY 子句決定 ROWNUM 順序） | 超長字符串可能超過 4000 字節限制 |
-- 11gR2+ 標準函數
SELECT LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) FROM test_sysconnectbypath;
-- 12c+ 併發安全
SELECT LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) OVER () FROM test_sysconnectbypath;
-- 19c+ JSON 聚合
SELECT JSON_ARRAYAGG(name ORDER BY name) FROM test_sysconnectbypath;WITH data AS (
SELECT deptno, ename, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) rn
FROM scott.emp
),
tree AS (
SELECT deptno, ename,
rn AS id,
LAG(rn) OVER (PARTITION BY deptno ORDER BY rn) AS pid
FROM data
)
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename, ',')), ',') AS employees
FROM tree
START WITH pid IS NULL
CONNECT BY PRIOR id = pid AND PRIOR deptno = deptno
GROUP BY deptno;利用 ROWNUM + LAG 構造虛擬父子關系，再配合 SYS_CONNECT_BY_PATH + CONNECT BY，巧妙實現了行轉列拼接。這是 Oracle 分析函數與層次查詢結合的經典「側寫」用法。現代版本首選 LISTAGG，但理解此技巧有助於深入掌握 CONNECT BY 機制與分析函數窗口排序邏輯。
