在編寫T-SQL代碼時(shí)，往往需要臨時(shí)存儲(chǔ)某些結(jié)果集。前面我們已經(jīng)廣泛使用和介紹了兩種臨時(shí)存儲(chǔ)結(jié)果集的方法：臨時(shí)表和表變量。除此之外，還可以使用公用表表達(dá)式的方法。

公用表表達(dá)式(Common Table Expression)是SQL Server2005版本的引入的一個(gè)特性。CTE可以看組是一個(gè)臨時(shí)的結(jié)果集，可以再接下來來的一個(gè)SELECT,INSERT,UPDATE,DELETE,MERGE語句中多次引用。

一、3種方法比較

使用公用表達(dá)式CTE可以讓語句更加清晰簡練。與公用表達(dá)式作用類似的還有臨時(shí)表和表變量。下面給出三種方法的對(duì)比。

• 臨時(shí)表#：需要在臨時(shí)數(shù)據(jù)庫TempDB中通過I/O操作來創(chuàng)建表結(jié)構(gòu)，一旦用戶退出SQL Server環(huán)境則自動(dòng)被刪除。
• 表變量@：在內(nèi)存中以表結(jié)構(gòu)的形式存在，其定義與變量一致，其使用與表類似，不需要產(chǎn)生I/O。
• 公用表表達(dá)式with as：定義在內(nèi)存中保存的臨時(shí)存儲(chǔ)結(jié)果集對(duì)象，不產(chǎn)生I/O，不需要按照表變量這樣定義，使用方法和表類似。可以自己引用，也可以再查詢中被多次引用。

1、使用CTE好處

根據(jù)微軟對(duì)CTE好處的描述，可以歸結(jié)為四點(diǎn):

• 可以定義遞歸公用表表達(dá)式(CTE)
• 當(dāng)不需要將結(jié)果集作為視圖被多個(gè)地方引用時(shí)，CTE可以使其更加簡潔
• GROUP BY 語句可以直接作用于子查詢所得的標(biāo)量列
• 可以在一個(gè)語句中多次引用公用表表達(dá)式(CTE)

二、WITH AS的含義

WITH AS-做子查詢部分(subquery factoring)。

它用于定義一個(gè)SQL片段，該片段會(huì)被是整個(gè)SQL語句所用到。如果WITH AS所以定的表名被調(diào)用兩次以上，則優(yōu)化器會(huì)自動(dòng)將WITH AS所獲取的數(shù)據(jù)放入臨時(shí)表里，如果只是被調(diào)用一次，則不會(huì)。

可以通過materialize將WITH AS短語里的數(shù)據(jù)強(qiáng)制放入全局臨時(shí)表里。

WITH AS可以被緊跟著的一條SQL語句所使用多次，但不能被緊跟著的多條SQL語句使用。