您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 通用資料表運算式 (CTE) 入門：簡化分頁查詢、遞歸查詢與可讀性提升

編輯：關於SqlServer

CTE (Common Table Expression，通用資料表運算式) 是 SQL Server 2005 引入的暫時命名結果集，僅在單一陳述式 (`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE VIEW`) 範圍內有效。CTE 不像暫存表佔用 `TempDB` 實體儲存，也不用像衍生表巢狀難以閱讀。本文透過分頁查詢實例展示 CTE 語法優勢，並介紹遞歸 CTE 應用場景。

一、基本語法結構


WITH cte_name (column_list) AS
(
    -- 定義查詢 (錨點成員)
    SELECT ...
)
-- 緊接著單一陳述式引用 CTE
SELECT * FROM cte_name WHERE ...;

可定義多個 CTE：`WITH cte1 AS (...), cte2 AS (SELECT * FROM cte1 ...) SELECT * FROM cte2;`
CTE 可自我參考 → **遞歸 CTE** (需 `MAXRECURSION` 控制)。

二、實戰範例：分頁查詢 (ROW_NUMBER + CTE)

建立測試資料


IF OBJECT_ID('t', 'U') IS NOT NULL DROP TABLE t;
GO
CREATE TABLE t (c1 INT, c2 DECIMAL, c3 INT);
GO
INSERT INTO t VALUES (1, 5.0, 10), (2, 5.5, 10), (3, 5.0, 20), (4, 5.5, 20);
GO

CTE 分頁查詢：取第 2-3 筆 (依 c1 DESC)


WITH t_cn AS
(
    SELECT c1, c2, c3,
           rn = ROW_NUMBER() OVER (ORDER BY c1 DESC)
    FROM t
    WHERE 1=1 -- 方便動態加條件
)
SELECT c1, c2, c3
FROM t_cn
WHERE rn BETWEEN 2 AND 3;

-- 同時取得總筆數 (分頁必要)
SELECT totalCn = COUNT(*) FROM t WHERE 1=1;

優勢對比

寫法	程式碼結構	可讀性	效能
巢狀衍生表	`SELECT * FROM (SELECT *, ROW_NUMBER()... FROM (SELECT ... FROM t) a) b WHERE rn...`	⭐⭐ (深層巢狀難追蹤)	相同
暫存表 #temp	建表 → 插入 → 查詢 → 刪除	⭐⭐⭐ (程序式)	額外 TempDB I/O、鎖定
CTE	`WITH t_cn AS (...) SELECT ... FROM t_cn WHERE ...`	⭐⭐⭐⭐⭐ (線性閱讀、邏輯清晰)	相同 (最佳化器展開)

三、進階應用：遞歸 CTE (階層結構查詢)

經典場景：員工組織架構、BOM 物料清單、分類樹狀結構。


-- 員工表：EmpId, MgrId, EmpName
WITH OrgChart AS
(
    -- 錨點成員：最上層 (CEO, MgrId IS NULL)
    SELECT EmpId, MgrId, EmpName, 0 AS Level
    FROM Employee
    WHERE MgrId IS NULL

    UNION ALL

    -- 遞歸成員：由上層往下查
    SELECT e.EmpId, e.MgrId, e.EmpName, oc.Level + 1
    FROM Employee e
    INNER JOIN OrgChart oc ON e.MgrId = oc.EmpId
)
SELECT * FROM OrgChart
OPTION (MAXRECURSION 100); -- 防無限遞歸，預設 100，0=無限制

四、 CTE 使用限制與注意事項

作用域：僅緊隨其後的**單一** T-SQL 陳述式可見 (不可跨批次、不可在同批次多陳述式共用)。
非持久化：不佔用 `TempDB`，不產生統計資訊，最佳化器視為查詢樹一部分展開。
遞歸終止**：必須有明確終止條件 (如 `Level < 10` 或 `MgrId IS NULL`)，並設定 `OPTION (MAXRECURSION n)` 防失控。
不可直接 UPDATE/DELETE CTE**：但可 `UPDATE t SET ... FROM cte JOIN t ON ...` 間接達成。
排序限制**：CTE 定義中不可使用 `ORDER BY` (除非搭配 `TOP`)，外層查詢才能排序。