SQL Server 2005 引入 CTE (Common Table Expression，共用資料表運算式)，提供宣告式、可重複引用、支援遞迴的暫時結果集，取代暫存表/表變數的許多用途。本文系統介紹 CTE 語法結構、優點、適用範圍，並以「遞迴階層查詢」為核心範例，完整對比 SQL 2000 游標/暫存表寫法與 CTE 遞迴寫法，展現 CTE 的簡潔威力。
WITH cte_name [(column_list)] AS (
-- 定義查詢 (錨點成員)
SELECT ...
)
-- 主查詢：可多次引用 CTE
SELECT * FROM cte_name;
| 特性 | 說明 | 對比暫存表/表變數 |
|---|---|---|
| 不持久化 | 僅在單一陳述式執行期間存在 | 暫存表需手動 DROP，表變數隨批次結束 |
| 可自我引用 (遞迴) | UNION ALL 結合錨點與遞迴成員 | 暫存表/表變數無法直接遞迴 |
| 同一查詢多次引用 | 主查詢、子查詢、JOIN 皆可重用 | 暫存表需重複 INSERT 或 JOIN 多次 |
| 可讀性 | 邏輯由上而下，宣告式 | 暫存表程序式、分散在多個陳述式 |
| 範圍 | 單一 SELECT/INSERT/UPDATE/DELETE/CREATE VIEW | 暫存表跨批次/程序可見 |
WITH MaxSalaryCTE (dept_id, max_sal) AS (
SELECT dept_id, MAX(salary) FROM Employee GROUP BY dept_id
)
SELECT e.*
FROM Employee e JOIN MaxSalaryCTE m ON e.dept_id = m.dept_id AND e.salary = m.max_sal;
CREATE TABLE Co_ItemNameSet (
ItemId INT PRIMARY KEY,
ParentItemId INT,
ItemName NVARCHAR(100)
);
-- 範例資料
INSERT INTO Co_ItemNameSet VALUES
(2, 0, N'管理費用'),
(3, 0, N'銷售費用'),
(5, 0, N'生產成本'),
(35, 5, N'材料'),
(36, 5, N'人工'),
(37, 5, N'製造費用'),
(38, 35, N'原材料'),
(39, 35, N'主要材料'),
(40, 35, N'輔助材料'),
(41, 36, N'工資'),
(42, 36, N'福利'),
(43, 36, N'年終獎金'),
(52, 2, N'汽車費用'),
(53, 52, N'燃料'),
(54, 52, N'輪胎'),
(55, 2, N'招聘費'),
(56, 2, N'排污費');
DECLARE @i INT = 2;
CREATE TABLE #stack (ItemId INT, lvl INT);
CREATE TABLE #result (ItemId INT, ParentItemId INT, ItemName NVARCHAR(100), lvl INT);
INSERT #stack SELECT ItemId, 1 FROM Co_ItemNameSet WHERE ItemId = @i;
INSERT #result SELECT ItemId, ParentItemId, ItemName, 1 FROM Co_ItemNameSet WHERE ItemId = @i;
DECLARE @lvl INT = 1, @current INT = 0;
WHILE @lvl > 0
BEGIN
SELECT @current = ItemId FROM #stack WHERE lvl = @lvl;
IF @@ROWCOUNT > 0
BEGIN
DELETE #stack WHERE lvl = @lvl AND ItemId = @current;
INSERT #stack SELECT ItemId, @lvl + 1 FROM Co_ItemNameSet WHERE ParentItemId = @current;
INSERT #result SELECT ItemId, ParentItemId, ItemName, @lvl + 1 FROM Co_ItemNameSet WHERE ParentItemId = @current;
IF @@ROWCOUNT > 0 SET @lvl += 1;
END
ELSE SET @lvl -= 1;
END
SELECT * FROM #result;
DROP TABLE #stack, #result;
DECLARE @i INT = 2;
WITH Co_ItemNameSet_CTE (ItemId, ParentItemId, ItemName, Lvl) AS (
-- 錨點成員：起始節點
SELECT ItemId, ParentItemId, ItemName, 1
FROM Co_ItemNameSet
WHERE ItemId = @i
UNION ALL
-- 遞迴成員：連接父子關係
SELECT c.ItemId, c.ParentItemId, c.ItemName, cte.Lvl + 1
FROM Co_ItemNameSet c
INNER JOIN Co_ItemNameSet_CTE cte ON c.ParentItemId = cte.ItemId
)
SELECT * FROM Co_ItemNameSet_CTE;
ItemId ParentItemId ItemName Lvl
2 0 管理費用 1
52 2 汽車費用 2
55 2 招聘費 2
56 2 排污費 2
53 52 燃料 3
54 52 輪胎 3
UNION ALL 上方) + 遞迴成員 (下方)JOIN Co_ItemNameSet_CTE)OPTION (MAXRECURSION 0) (0=無限制) 或具體數字WITH Dates AS (
SELECT CAST('2024-01-01' AS DATE) AS dt
UNION ALL
SELECT DATEADD(DAY, 1, dt) FROM Dates WHERE dt < '2024-12-31'
)
SELECT * FROM Dates OPTION (MAXRECURSION 0);
WITH Split AS (
SELECT 1 AS id,
LEFT(@str, CHARINDEX(',', @str+',')-1) AS val,
STUFF(@str, 1, CHARINDEX(',', @str+','), '') AS rest
UNION ALL
SELECT id+1, LEFT(rest, CHARINDEX(',', rest+',')-1), STUFF(rest, 1, CHARINDEX(',', rest+','), '')
FROM Split WHERE rest > ''
)
SELECT id, val FROM Split;
WITH OrgPath AS (
SELECT EmployeeID, ManagerID, Name, CAST(Name AS NVARCHAR(MAX)) AS Path, 1 AS Lvl
FROM Employee WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.Name, CAST(o.Path + ' > ' + e.Name AS NVARCHAR(MAX)), o.Lvl + 1
FROM Employee e JOIN OrgPath o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgPath;
HierarchyID (SQL 2008+) 或物化路徑MAXRECURSION 0 謹慎使用，防止無限遞迴耗盡資源INSERT、UPDATE、DELETE、MERGE、CREATE VIEWHierarchyID 型別優化階層)
