萬盛學電腦網

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

SQL 遞迴查詢實戰:父子層級關聯路徑拼接(CTE vs 游標方案對比)

處理「父子級 ID 關聯」的層級結構(如地區、組織架構、分類樹)時,需將每個節點的所有祖先名稱拼接成路徑字串。本文對比 遞迴 CTE(公用表表達式) 與 游標+迴圈 兩種方案,展示 SQL Server 實作碼、效能差異與適用場景。

問題定義

表 AreaRegion:

AreaCode (PK) | AreaName | ParentCode (FK → AreaCode)
--------------------------------------------------
1             | 浙江省    | 0
2             | 紹興市    | 1
3             | 越城區    | 2

目標:新增 Content 欄位,存儲從頂級到自身的完整路徑:浙江省,紹興市,越城區

方案一:遞迴 CTE(推薦,集合思維、效能優)

WITH cte (areacode, areaName, content) AS (
    -- 錨點:頂級節點
    SELECT areacode, areaName, CAST(areaName AS VARCHAR(500)) AS content
    FROM AreaRegion
    WHERE parentcode = 0

    UNION ALL

    -- 遞迴:子節點接父節點路徑
    SELECT a.areacode, a.areaName, 
           CAST(b.content + ',' + a.areaName AS VARCHAR(500))
    FROM AreaRegion a
    INNER JOIN cte b ON a.parentcode = b.areacode
)
-- 寫回原表
UPDATE a
SET a.Content = c.content
FROM AreaRegion a
INNER JOIN cte c ON a.areacode = c.areacode;

優勢

  • 單條 SQL 完成,無需程式語言配合
  • 優化器可生成高效執行計畫(流水線式遞迴)
  • 可攜性強:PostgreSQL、MySQL 8.0+、Oracle、SQL Server 均支援

注意事項

  • MAXRECURSION 預設 100 層,深層樹需 OPTION (MAXRECURSION 0) 移除限制
  • 循環引用會無限遞迴,需資料層面保證無環或加深度檢查
  • VARCHAR 長度預估:層數 × 平均名稱長度 + 分隔符

方案二:游標 + 迴圈(程序化、靈活但慢)

USE JKCRM;
GO

DECLARE @TempTable TABLE (AreaCode INT PRIMARY KEY, Content NVARCHAR(3000));

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT AreaCode FROM dbo.AreaRegion;

DECLARE @aID INT, @pStr NVARCHAR(3000);

OPEN cur;
FETCH NEXT FROM cur INTO @aID;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @pStr = '';

    -- 遞迴 CTE 取單節點路徑
    WITH TB AS (
        SELECT AreaCode, AreaName, ParentCode, 0 AS LVL
        FROM dbo.AreaRegion WHERE AreaCode = @aID
        UNION ALL
        SELECT r.AreaCode, r.AreaName, r.ParentCode, LVL + 1
        FROM TB t
        INNER JOIN dbo.AreaRegion r ON t.ParentCode = r.AreaCode
    )
    SELECT @pStr = @pStr + CASE WHEN @pStr = '' THEN '' ELSE ',' END + AreaName
    FROM TB
    ORDER BY LVL ASC;  -- 由根至葉

    INSERT INTO @TempTable VALUES (@aID, @pStr);

    FETCH NEXT FROM cur INTO @aID;
END

CLOSE cur;
DEALLOCATE cur;

-- 批次更新
UPDATE a
SET a.Content = t.Content
FROM dbo.AreaRegion a
INNER JOIN @TempTable t ON a.AreaCode = t.AreaCode;

劣勢

  • RBAR(Row By Agonizing Row):N 個節點 = N 次查詢 + N 次插入
  • 鎖持有時間長,併發性差
  • 程式碼冗長、維護成本高

效能對比(3000 筆測試資料)

MySQL 8.0+ 寫法(CTE 通用)

WITH RECURSIVE cte AS (
    SELECT areacode, areaname, CAST(areaname AS CHAR(500)) AS content
    FROM AreaRegion WHERE parentcode = 0
    UNION ALL
    SELECT a.areacode, a.areaname, CONCAT(b.content, ',', a.areaname)
    FROM AreaRegion a JOIN cte b ON a.parentcode = b.areacode
)
UPDATE AreaRegion a
JOIN cte c ON a.areacode = c.areacode
SET a.Content = c.content;

緩存策略建議(專案實戰)

文章提到:專案中通常將全表讀入記憶體(Dictionary/Map),應用層遞迴組裝路徑,再批次更新 DB。此法適用於:

  • 資料量較小(< 10 萬)、變更頻率低
  • 應用層語言擅長圖演算法(Java/Go/Python)
  • 需複雜業務邏輯(權限檢查、條件過濾)

但純 SQL CTE 方案零依賴、可直接在資料庫排程執行,更適合:

  • ETL/數倉夜ly 批次重建
  • 無應用層介入的 DBA 維護腳本
  • 跨語言、跨框架的通用解法

結論:能用 CTE 就別用游標。集合操作是關係型資料庫的靈魂,遞迴 CTE 將層級查詢從「程式邏輯」拉回「宣告式 SQL」,不僅快 40 倍,更消除了 RBAR 的資源浪費與併發風險。

copyright © 萬盛學電腦網 all rights reserved
指標遞迴 CTE游標迴圈
執行時間~200ms~8s
邏輯讀取~5,000~120,000
TempDB 壓力低高(表變量+游標)
鎖持有時間短(單語句)長(顯式事務)