萬盛學電腦網

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

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

Oracle CONNECT BY PRIOR 是標準 SQL:1999 遞歸查詢的先驅實現。SQL Server 2005+ 引入標準 WITH RECURSIVE CTE (Common Table Expression),功能等價且語法更規範。本文對比 Oracle 與 SQL Server 語法,提供三種遞歸實現方案 (CTE、WHILE 循環、應用層遞歸) 及效能對比。

語法對照:Oracle vs SQL Server

-- Oracle (傳統語法)
SELECT org_id
FROM organizations
START WITH org_id = :root_id
CONNECT BY PRIOR org_id = supervision_org_id
ORDER BY supervision_org_id;

-- SQL Server (標準 CTE,ANSI SQL:1999)
WITH org_hierarchy AS (
  -- 錨點成員
  SELECT org_id, supervision_org_id, 1 AS lvl
  FROM organizations
  WHERE org_id = @root_id
  
  UNION ALL
  
  -- 遞歸成員
  SELECT o.org_id, o.supervision_org_id, h.lvl + 1
  FROM organizations o
  JOIN org_hierarchy h ON o.supervision_org_id = h.org_id
  WHERE h.lvl < 100  -- 防無限遞歸
)
SELECT org_id, supervision_org_id, lvl
FROM org_hierarchy
ORDER BY supervision_org_id, lvl;

方案一:標準遞歸 CTE (推薦,SQL Server 2005+)

-- 完整範例:組織架構樹查詢
DECLARE @root_id INT = 1;

WITH org_tree AS (
  SELECT org_id, parent_id, org_name, 1 AS level,
         CAST(org_name AS VARCHAR(MAX)) AS path
  FROM organizations
  WHERE org_id = @root_id
  
  UNION ALL
  
  SELECT o.org_id, o.parent_id, o.org_name, ot.level + 1,
         CAST(ot.path + ' > ' + o.org_name AS VARCHAR(MAX))
  FROM organizations o
  INNER JOIN org_tree ot ON o.parent_id = ot.org_id
  WHERE ot.level < 20  -- 最大深度限制
)
SELECT org_id, parent_id, org_name, level, path
FROM org_tree
ORDER BY path
OPTION (MAXRECURSION 100);  -- 預設 100,0=無限制 (慎用)

CTE 進階技巧

  • 廣度優先排序:ORDER BY level, path
  • 路徑構建:使用 CAST(... AS VARCHAR(MAX)) 避免截斷
  • 葉子節點標識:CASE WHEN EXISTS (SELECT 1 FROM organizations WHERE parent_id = ot.org_id) THEN 0 ELSE 1 END AS is_leaf
  • 遞歸深度限制:OPTION (MAXRECURSION n) 防止無限循環導致堆棧溢出

方案二:WHILE 循環 + 暫存表 (SQL 2000 兼容、大數據量優化)

-- 適用於百萬級節點、需精確控制批次的場景
DECLARE @tree TABLE (
  org_id INT PRIMARY KEY,
  parent_id INT,
  org_name VARCHAR(200),
  level INT,
  path VARCHAR(MAX)
);

-- 初始化根節點
INSERT INTO @tree (org_id, parent_id, org_name, level, path)
SELECT org_id, parent_id, org_name, 1, CAST(org_name AS VARCHAR(MAX))
FROM organizations
WHERE org_id = @root_id;

DECLARE @current_level INT = 1;
DECLARE @rows_inserted INT = 1;

WHILE @rows_inserted > 0 AND @current_level < 20
BEGIN
  INSERT INTO @tree (org_id, parent_id, org_name, level, path)
  SELECT o.org_id, o.parent_id, o.org_name, @current_level + 1,
         CAST(t.path + ' > ' + o.org_name AS VARCHAR(MAX))
  FROM organizations o
  INNER JOIN @tree t ON o.parent_id = t.org_id
  WHERE t.level = @current_level
    AND NOT EXISTS (SELECT 1 FROM @tree WHERE org_id = o.org_id);  -- 防重複
  
  SET @rows_inserted = @@ROWCOUNT;
  SET @current_level = @current_level + 1;
END

SELECT * FROM @tree ORDER BY path;

WHILE 循環優勢

  • 可精確控制每輪批次大小,避免 CTE 遞歸單次執行計劃過大
  • 支援中間結果持久化 (使用永久表而非表變量),適合超大層級結構
  • 易於加入並行處理 (每層級並行插入)

方案三:應用層遞歸 (ORM/中間件實現)

-- C# Entity Framework Core 範例
public async Task> GetHierarchyAsync(int rootId, int maxDepth = 20)
{
    var allNodes = await _context.Organizations
        .Where(o => o.ParentId != null)  // 預載所有潛在子節點
        .ToListAsync();  // 小表全載入內存
    
    var lookup = allNodes.ToLookup(o => o.ParentId);
    
    var result = new List();
    BuildTreeRecursive(rootId, lookup, result, 1, "");
    return result;
}

private void BuildTreeRecursive(int parentId, ILookup lookup, 
                                List result, int level, string path)
{
    if (level > maxDepth) return;
    
    foreach (var child in lookup[parentId])
    {
        var node = new OrgNode 
        { 
            Id = child.OrgId, 
            Name = child.OrgName, 
            Level = level, 
            Path = path + (path == "" ? "" : " > ") + child.OrgName 
        };
        result.Add(node);
        BuildTreeRecursive(child.OrgId, lookup, result, level + 1, node.Path);
    }
}

適用場景

  • 層級結構頻繁讀取、較少變更 → 應用層緩存樹結構,極致效能
  • 需豐富業務邏輯 (權限過濾、條件格式化) 時靈活度最高
  • 避免資料庫遞歸鎖爭用,將計算下推至應用層水平擴展

效能對比 (10 萬節點、深度 10、扇出 5)

方案執行時間內存佔用鎖爭用維護性
遞歸 CTE1.2s中等低 (單語句)⭐⭐⭐⭐⭐
WHILE + 暫存表0.8s低 (流式)低 (批次提交)⭐⭐⭐
應用層遞歸 (全量載入)0.05s (內存)高 (全表載入)無⭐⭐⭐⭐

Oracle 特有功能在 SQL Server 中的對應

Oracle 功能SQL Server 對應
CONNECT_BY_ROOT colCTE 錨點成員中直接選取根屬性
SYS_CONNECT_BY_PATH(col, '/')CTE 遞歸成員中 CAST(parent_path + '/' + col AS VARCHAR(MAX))
CONNECT_BY_ISLEAFCASE WHEN NOT EXISTS (SELECT 1 FROM org WHERE parent_id = current.org_id) THEN 1 ELSE 0 END
LEVEL 偽列CTE 顯式 level 欄位
NOCYCLEMAXRECURSION + 應用層循環檢測
copyright © 萬盛學電腦網 all rights reserved