MSSQL/SQL Server 中的 CTE：應用與範例

什麼是CTE

全名為 Common Table Expressions，中文翻譯為 公共表表達式 或是 一般資料表運算式

是一種特殊的查詢結果，生命週期僅限於使用它的查詢或語句的執行過程中。一旦該查詢或語句執行完成，CTE 就會消失

與臨時表(#、##)的比較

CTE 簡單範例

以下都拿一個功能清單的Table作為範例

CTE用起來也很簡單 ，XXX可以是任意名稱，跟temp table 差不多的

with XXX as (
   select 語法
)

⬇來個最簡單的

這樣的 select * from cte 其實背後就是 select * from (select * from SysFunction)

⬆️可以看到第二次呼叫，他就翻臉不認人了 (無效的物件名稱)

CTE 與 臨時表簡單查詢的比較

接下來，我們來看看要做出同樣的事情，CTE語法 與 create temp table 到底有多少差異

第一位選手，CTE

簡潔有力，我給 10分

第二位選手，# 臨時表

可以看到同樣要這4個欄位變成這麼多行

上面的這個範例還是比較簡單的，實際上運用可能遠遠超過4個吧…

另外，with 語法一次也可以定義多個資料來源，像是下面的 cte、cte2、cte3

但要注意的是，離開 with 後，也只能用一次的語法來執行它

意思是，你想要 cte union all cte union all cte 個100次都可以，那還算在一次的執行中

CTE遞迴範例

最後，我們來看看我最常用CTE的時候，就是要做階層的時候

在with語法中使用了 inner join 本身，並取得下一層的資料

結論

CTE其實算是個容易上手的語法，但比起臨時表來說還是有著不少的限制，但勝在它方便且簡潔

另外，CTE的遞迴查詢在查詢階層式資料上非常方便，不用特別去寫到後端的Code去慢慢寫

另外，CTE的多個查詢的寫法也讓SQL的 DEBUG 變得簡單多了，當複雜的SQL出問題，可以拿出特定的一段來執行就好

參考資料

• 我聰明的小腦袋
• [SQL] 使用 CTE 遞迴查詢 (PostgreSQL / MSSQL)：叡揚部落格 (gss.com.tw)

🧡希望這篇文章有提供一點幫助~

🧡幫我點一個小小的廣告或留言，都是對我的支持!

⭐如有任何疑問，歡迎透過留言或messenger讓我知道 !

🍽️另外，徵求任何合作機會(不管是業配、廣告或是友站連結)

看看一些其他的筆記吧