關於 SQL 的 Common Table Expression

Common Table Expression (CTE) 是 SQL Server 2005 開始加入的新成員，千萬別跟 ETC 搞混囉（雖然把名字倒過來唸還真的一模一樣）！！

會對 CTE 開始有興趣，是因為以前在撰寫比較複雜且多層的子查詢時，自己當下是很清楚要寫什麼，可是經過一段時間後，自己已經有點不懂當時的想法，更不用說其他接手維護的工程師了。舉例來說，以前寫的好幾層子查詢，可能長得如下：

select A1 from A where A2 in
 (
        select B1 from B where B2 in
                    ( select C1 from C where C2 like '%test%')
 )

透過 CTE，可以讓我們不用這麼辛苦，可以暫時先把每個子查詢的結果存放在一個記憶體變數中，而結果會如下：

;with tC as
      (select C1 from C where C2 like '%test%')
,tB as
      ( select B from B where B2 in (select * from tC))
select A1 from A where A2 in (select * from tB)

在 Insert 的用法：

create table #tmp (Country nvarchar(200),OkNum int)

;with T as
(select Country,OkNum from AsiaData )
insert into #tmp select Country,OkNum from T

select * from #tmp
drop table #tmp

在 Group 的用法：

;with T as
(
   select Country,OkNum from AsiaData union select Country,OkNum from EuropeData
)
select Country,sum(isnull(OkNum,0)) OkNum from T
group by Country

個人覺得，使用 CTE，程式碼不見得會寫得比較少，但至少對閱讀上來說，的確是比較容易理解。在使用上，除了一般所撰寫的 T-SQL 查詢之外，還可將 CTE 用於使用者自訂的函數( Function)、預存程序( Stored Procedure)、檢視( View )、觸發程序( Triggers )。

CTE 語法的基本架構如下(Ref 01:Using Common Table Expressions)：

--定義 CTE
;WITH CTE_運算式名稱 [ ( column_name [,...n] ) ]
AS
( CTE_查詢_定義 )

--使用 CTE
SELECT <column_list> FROM CTE_運算式名稱;

在定義 CTE 時，如果沒有指定欄位名稱，則結果的欄位名稱預設會跟 CTE_查詢_定義 裡的結果一樣；如果有指定欄位名稱，則欄位名稱的值會跟 CTE_查詢_定義 裡出現的結果順序一樣，且 CTE 的欄位名稱是不可重複的。舉例來說：

;with T (T1,T2) as
     (select C2,C1 from C )

這裡要注意，CTE 的結果裡，T1 欄位所呈現的值會是 資料表 C 的 C2 欄位，原因是 CTE 的結果欄位是依據 CTE_查詢_定義 的順序來決定。(Ref 02:SQL with)

使用 CTE ，有個比較特別的現象，他並不像 View 或 暫存資料表一樣可以一直被引用，實際上，他只能被引用一次。也就是說，當你定義完 CTE 的結構後，你必須在後面接著馬上使用他，使用完了之後，他就不見了。

;with EuropeData (Country,OkNum) as
(
   select top 5 Country,OkNum from AsiaData

)
-- 第一次查詢
select top 5 Country,OkNum from EuropeData
-- 第二次查詢
select top 5 Country,OkNum from EuropeData

在上面的範例中，一開始我定義了 CTE 的運算名稱為 EuropeData ，其實這名稱也剛好是我資料庫裡的某一個資料表，只是我刻意把 CTE 的名稱取的跟我既有的資料表名稱一致，只是 CTE 的結果，其實是從 AsiaData 而來。當定義完我的 CTE 之後，連續 2 次執行查詢，則可以在結果中發現，第一次的查詢，資料是從 AsiaData 而來。但第二次的查詢，因為自訂 CTE 的記憶體空間已被回收，所以會抓到原先資料庫已經存在的 EuropeData 資料表。

CTE 除了上述的介紹之外，他還有另一個蠻有趣的主題：遞迴，這功能可以讓我們在處理行父子關係的資料表、組織圖，更有效率，於後面再詳述。

Ref：

01.Using Common Table Expressions
02.SQL with
03.SQL中使用WITH AS提高性能－使用公用表表达式（CTE）简化嵌套SQL
04.一般資料表運算式(Common Table Expressions, CTE)
05.T-SQL -- COMMON TABLE EXPRESSION (CTE) 教學重點筆記
06.SQL 2005 T-SQL Enhancement: Common Table Expression
07.Performance Effect of Common Table Expressions in SQL Server 2005