處理「父子級 ID 關聯」的層級結構(如地區、組織架構、分類樹)時,需將每個節點的所有祖先名稱拼接成路徑字串。本文對比 遞迴 CTE(公用表表達式) 與 游標+迴圈 兩種方案,展示 SQL Server 實作碼、效能差異與適用場景。
表 AreaRegion:
AreaCode (PK) | AreaName | ParentCode (FK → AreaCode)
--------------------------------------------------
1 | 浙江省 | 0
2 | 紹興市 | 1
3 | 越城區 | 2
目標:新增 Content 欄位,存儲從頂級到自身的完整路徑:浙江省,紹興市,越城區
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;
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;
| 指標 | 遞迴 CTE | 游標迴圈 |
|---|---|---|
| 執行時間 | ~200ms | ~8s |
| 邏輯讀取 | ~5,000 | ~120,000 |
| TempDB 壓力 | 低 | 高(表變量+游標) |
| 鎖持有時間 | 短(單語句) | 長(顯式事務) |