萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Oracle CONNECT BY 層級查詢在 SQL Server 中的實現方案

遞歸查詢跨平台遷移:Oracle CONNECT BY → SQL Server 遞歸 CTE

Oracle 的 CONNECT BY 是處理樹狀/層級結構(組織架構、BOM、分類目錄)的利器。SQL Server 2005+ 引入遞歸 CTE (Common Table Expression),功能相當且符合 ANSI SQL 標準。本文對比兩種寫法,並提供通用遷移模板。

Oracle 寫法回顧

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)。

SQL Server 遞歸 CTE 標準寫法

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 BYSQL 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

進階技巧與避坑

  1. 防無限遞歸:SQL Server 預設最大遞歸 100 層(MAXRECURSION 100)。深層樹需加 OPTION (MAXRECURSION 0) 移除限制(0=無限制),但小心死循環。
  2. 路徑串聯:SELECT ..., CAST(TC_Name AS VARCHAR(MAX)) AS Path FROM ... UNION ALL SELECT ..., rt.Path + ' > ' + a.TC_Name ...。
  3. 效能優化:父子關聯欄位(TC_PID、TC_Id)必須建索引;大數據量考慮物化路徑或 Closure Table 設計。
  4. 舊版 SQL Server (2000) 替代方案:臨時表 + WHILE 迴圈(原文範例),效能差、難維護,僅供歷史系統參考。

其他資料庫對應

  • PostgreSQL:支援標準遞歸 CTE,語法與 SQL Server 幾乎相同。
  • MySQL 8.0+:支援遞歸 CTE,語法同標準 SQL。
  • MySQL 5.7-:無原生遞歸,需用儲存過程/觸發器/應用層遞歸/Closure Table。
copyright © 萬盛學電腦網 all rights reserved