Oracle 的 CONNECT BY 是處理樹狀/層級結構(組織架構、BOM、分類目錄)的利器。SQL Server 2005+ 引入遞歸 CTE (Common Table Expression),功能相當且符合 ANSI SQL 標準。本文對比兩種寫法,並提供通用遷移模板。
SELECT org_id
FROM organizations
START WITH org_id = :org_id
CONNECT BY PRIOR org_id = supervision_org_id
ORDER BY supervision_org_id;
START WITH:指定根節點。CONNECT BY PRIOR:定義父子關係(PRIOR 指向父層欄位)。LEVEL 表示層級深度(根=1)。WITH OrgTree AS (
-- 錨點成員:根節點
SELECT org_id, supervision_org_id, 1 AS lvl
FROM organizations
WHERE org_id = @org_id
UNION ALL
-- 遞歸成員:子節點關聯父節點
SELECT o.org_id, o.supervision_org_id, ot.lvl + 1
FROM organizations o
INNER JOIN OrgTree ot ON o.supervision_org_id = ot.org_id
)
SELECT org_id, supervision_org_id, lvl
FROM OrgTree
ORDER BY supervision_org_id, lvl;
| Oracle CONNECT BY | SQL Server 遞歸 CTE |
|---|---|
START WITH | 錨點成員(第一個 SELECT) |
CONNECT BY PRIOR child = parent | 遞歸成員 JOIN 邏輯 ON child.parent_id = parent.id |
隱含 LEVEL | 自定義欄位 lvl 累加 |
SYS_CONNECT_BY_PATH 路徑串聯 | 遞歸成員中 path + '>' + name 累加 |
NOCYCLE 防循環 | 需自行加 MAXRECURSION 或邏輯檢查 |
-- 建立測試資料
DECLARE @a TABLE (TC_Id INT, TC_PID INT, TC_Name VARCHAR(200));
INSERT @a VALUES
(1,0,'中國'),(2,0,'美國'),(3,0,'加拿大'),
(4,1,'北京'),(5,1,'上海'),(6,1,'江蘇'),
(7,6,'蘇州'),(8,7,'常熟'),(9,6,'南京'),(10,6,'無錫'),
(11,2,'紐約'),(12,2,'舊金山');
-- 遞歸 CTE 查詢中國(1)及其所有下級
WITH RegionTree AS (
SELECT TC_Id, TC_PID, TC_Name, 1 AS Level
FROM @a
WHERE TC_Id = 1
UNION ALL
SELECT a.TC_Id, a.TC_PID, a.TC_Name, rt.Level + 1
FROM @a a
INNER JOIN RegionTree rt ON a.TC_PID = rt.TC_Id
)
SELECT TC_Id, TC_PID, TC_Name, Level
FROM RegionTree
ORDER BY Level, TC_Id;
TC_Id TC_PID TC_Name Level
1 0 中國 1
4 1 北京 2
5 1 上海 2
6 1 江蘇 2
7 6 蘇州 3
9 6 南京 3
10 6 無錫 3
8 7 常熟 4
MAXRECURSION 100)。深層樹需加 OPTION (MAXRECURSION 0) 移除限制(0=無限制),但小心死循環。SELECT ..., CAST(TC_Name AS VARCHAR(MAX)) AS Path FROM ... UNION ALL SELECT ..., rt.Path + ' > ' + a.TC_Name ...。TC_PID、TC_Id)必須建索引;大數據量考慮物化路徑或 Closure Table 設計。WHILE 迴圈(原文範例),效能差、難維護,僅供歷史系統參考。