Oracle CONNECT BY 遞歸查詢在 SQL Server 中的三種實現方案

Oracle CONNECT BY 是處理層級/樹狀結構（組織架構、分類目錄、BOM 物料清單）的利器。SQL Server 無原生 CONNECT BY，本文提供三種等效實現：**遞歸 CTE (標準)、WHILE 循環臨時表 (舊版)、應用層遞歸**，附帶完整代碼與性能對比。

一、 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;

二、 方案一：遞歸 CTE (Common Table Expression) — SQL Server 2008+ 推薦標準

WITH OrgTree AS (
    -- 錨點成員：根節點
    SELECT org_id, supervision_org_id, 1 AS level
    FROM organizations
    WHERE org_id = @root_id

    UNION ALL

    -- 遞歸成員：子節點
    SELECT o.org_id, o.supervision_org_id, ot.level + 1
    FROM organizations o
    INNER JOIN OrgTree ot ON o.supervision_org_id = ot.org_id
)
SELECT org_id, supervision_org_id, level
FROM OrgTree
ORDER BY level, supervision_org_id;

優勢： ANSI SQL 標準、可讀性強、優化器支援良好、可控制最大遞歸深度 OPTION (MAXRECURSION 100)。

三、 方案二：WHILE 循環 + 臨時表 — SQL Server 2000/2005 兼容

-- 1. 建立臨時表存儲結果
DECLARE @tmp TABLE (org_id INT, supervision_org_id INT, level INT);

-- 2. 插入根節點
INSERT INTO @tmp (org_id, supervision_org_id, level)
SELECT org_id, supervision_org_id, 1
FROM organizations
WHERE org_id = @root_id;

-- 3. 循環遞歸查找子節點
WHILE @@ROWCOUNT > 0
BEGIN
    INSERT INTO @tmp (org_id, supervision_org_id, level)
    SELECT o.org_id, o.supervision_org_id, t.level + 1
    FROM organizations o
    INNER JOIN @tmp t ON o.supervision_org_id = t.org_id
    WHERE t.level = (SELECT MAX(level) FROM @tmp)  -- 僅展開最新層級
      AND NOT EXISTS (SELECT 1 FROM @tmp WHERE org_id = o.org_id); -- 防重複
END

-- 4. 輸出結果
SELECT * FROM @tmp ORDER BY level, supervision_org_id;

適用： 不支援 CTE 的極舊版本。性能較 CTE 差，大數據集易慢。

四、 方案三：應用層遞歸 (C#/Java/Python) — 極深層級/極大數據量

當樹深度 > 1000 (超過 MAXRECURSION 32767 限制) 或需複雜業務邏輯時，應用層分頁加載更靈活：

-- 單次查詢直接子節點 (配合索引極快)
SELECT org_id, supervision_org_id FROM organizations WHERE supervision_org_id = @parent_id;

應用層代碼 (C# 示例)：

public List BuildTree(int rootId) {
    var allNodes = db.Query<OrgNode>("SELECT org_id, supervision_org_id FROM organizations");
    var lookup = allNodes.ToLookup(n => n.supervision_org_id);
    return BuildNode(rootId, lookup);
}

private OrgNode BuildNode(int id, ILookup lookup) {
    var node = new OrgNode { Id = id, Children = new List() };
    foreach (var child in lookup[id]) 
        node.Children.Add(BuildNode(child.org_id, lookup));
    return node;
}

優勢： 無深度限制、內存中構建極快、支援複雜轉換（JSON 輸出、權限過濾）。

五、 完整測試數據與驗證 (方案二完整版)

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,'舊金山');

DECLARE @tmp1 TABLE (TC_Id INT, TC_PID INT, TC_Name VARCHAR(200), lev INT);
INSERT @tmp1 SELECT *, 1 FROM @a WHERE TC_Id = 1;

WHILE EXISTS (
    SELECT 1 FROM @a a INNER JOIN @tmp1 b ON a.TC_PID = b.TC_Id 
    WHERE a.TC_Id NOT IN (SELECT TC_Id FROM @tmp1)
)
INSERT @tmp1 
SELECT a.*, b.lev + 1 
FROM @a a INNER JOIN @tmp1 b ON a.TC_PID = b.TC_Id 
WHERE a.TC_Id NOT IN (SELECT TC_Id FROM @tmp1);

SELECT * FROM @tmp1 ORDER BY lev, TC_PID;

六、 性能與選型建議

七、 索引優化關鍵

-- 必須建立父節點索引，支援快速查找子節點
CREATE INDEX IX_organizations_supervision_org_id ON organizations (supervision_org_id);

無索引時，遞歸查詢每層全表掃描，性能指數級下降。

八、 總結

Oracle CONNECT BY 在 SQL Server 中，**遞歸 CTE 是標準現代解法**，**WHILE 循環是舊版兼容方案**，**應用層遞歸是極限場景補位**。配合父節點索引、MAXRECURSION 控制、應用層緩存，可高效支撐組織架構、多級分類、BOM 展開等所有樹狀查詢場景。