通用資料表運算式（CTE）

通用資料表運算式（CTE）是SQL Server 2005中提供的一種新的解決方案。

通用資料表運算式 (CTE) 可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執行範圍內定義的臨時結果集。CTE 與派生表類似，具體表現在不儲存為對象，並且只在查詢期間有效。與派生表的不同之處在於，CTE 可自引用，還可在同一查詢中引用多次。

CTE 作用：
（1）通用資料表運算式 (CTE) 能夠引用其自身，從而建立遞迴 CTE。
（2）在不需要常規使用視圖時替換視圖，也就是說，不必將定義儲存在中繼資料中。
（3）啟用按從標量嵌套 select 語句派生的列進行分組，或者按不確定性函數或有外部存取的函數進行分組。
（4）在同一語句中多次引用產生的表。
（5）可以在使用者定義的常式（如函數、預存程序、觸發器或視圖）中定義 CTE。
使用 CTE 可以獲得提高可讀性和輕鬆維護複雜查詢的優點。查詢可以分為單獨塊、簡單塊、邏輯產生塊。之後，這些簡單塊可用於產生更複雜的臨時 CTE，直到產生最終結果集。

CTE 的結構
CTE 由表示 CTE 的運算式名稱、可選列列表和定義 CET 的查詢組成。定義 CTE 後，可以在 SELECT、INSERT、UPDATE 或 DELETE 語句中對其進行引用，就像參考資料表或視圖一樣。CTE 也可用於 CREATE VIEW 語句，作為定義 SELECT 語句的一部分。

CTE 文法結構：

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

只有在查詢定義中為所有結果列都提供了不同的名稱時，列名稱列表才是可選的。

運行 CTE 的語句為：

SELECT

FROM expression_name

使用CTE時應注意點：
（1）CTE後面必須直接跟使用CTE的SQL語句，否則，CTE將失效。

WITH crs
AS(SELECT 編號 FROM EMPLOYER WHERE 員工 LIKE 'D%' )
--SELECT *FROM PERSON   -CTE後須直接跟使用CTE的語句，否則報錯
SELECT *FROM DEPART WHERE 員工編號 IN (SELECT * FROM crs)

（2）CTE後可以接多個CTE，但只能使用一個with，多個CTE中間用逗號“，”分隔。

如 尋找以‘D’開頭、且年齡不小於平均年齡的員工的部門和工資

WITH
crs  AS(SELECT 編號 FROM EMPLOYER WHERE 員工 LIKE 'D%' ),
crs2 AS(SELECT AVG(年齡)AS平均年齡  FROM EMPLOYER ),
crs3 AS (SELECT 編號 FROM EMPLOYER WHERE 年齡>=(SELECT * FROM crs2) AND 編號 IN  (SELECT * FROM crs))

SELECT *FROM DEPART WHERE 員工編號 IN (SELECT * FROM crs3)

（3）不能在 CTE_query_definition 中使用以下子句：

COMPUTE 或 COMPUTE BY、ORDER BY（除非指定了 TOP 子句）、INTO 、帶有查詢提示的 OPTION 子句、FOR XML、FOR BROWSE

舉例：
建立連個表並插入資料

CREATE TABLE EMPLOYER(編號 int,員工 char(6),年齡 int)
--插入資料
INSERT INTO EMPLOYER SELECT 1,'ZHANG',20
INSERT INTO EMPLOYER SELECT 2,'LI',21
INSERT INTO EMPLOYER SELECT 3,'WANG',22
INSERT INTO EMPLOYER SELECT 4,'ZHAO',23
INSERT INTO EMPLOYER SELECT 5,'DUAN',24
INSERT INTO EMPLOYER SELECT 6,'DUAN',25

CREATE TABLE DEPART(部門 char(10),員工編號 int,工資 int)
--插入資料
INSERT INTO DEPART SELECT 'A',1,100
INSERT INTO DEPART SELECT 'A',2,200
INSERT INTO DEPART SELECT 'A',3,300
INSERT INTO DEPART SELECT 'A',4,400
INSERT INTO DEPART SELECT 'B',5,500
INSERT INTO DEPART SELECT 'B',6,600

查詢以‘D’開頭員工的部門和工資
（1）嵌套
SELECT *
FROM DEPART
WHERE 員工編號 IN (SELECT 編號 FROM EMPLOYER WHERE 員工 LIKE 'D%' )
ORDER BY 部門,員工編號

結果：

部門   員工編號  工資

B             5     500
B             6     600

（2）公用運算式

WITH crs
AS(SELECT 編號 FROM EMPLOYER WHERE 員工 LIKE 'D%' )
SELECT *
FROM DEPART WHERE 員工編號 IN (SELECT * FROM crs)

結果：

部門   員工編號  工資

B             5     500
B             6     600

（3）表變數

DECLARE @TAB TABLE(編號 int)
INSERT INTO @TAB(編號)
(SELECT 編號 FROM EMPLOYER WHERE 員工 LIKE 'D%')
SELECT *
FROM DEPART WHERE 員工編號 IN (SELECT * FROM @TAB)

部門   員工編號  工資

B             5     500
B             6     600