CTE 筆記

若在較複雜的查詢，有時會使用到暫存表，但又太想建立暫存表，此時就可以搭配CTE來實作，

而CTE的出現，造成當時一波風潮，如不CTE 母寧死的鐵血捍衛...，接著我們來看看相關應用與實作。

CTE 全名為：Common Table Expression

CTE 主要應用為「簡化子查詢」與「遞迴查詢」，搭配使用可減少語法一層包一層，增加可讀性，

亦可讓單一陳述式共享CTE資料。

看一下官網的語法

從上面看來，語法從 WITH 關鍵字開始定義，後面接著名稱與欄位清單，在AS 括弧內為

query 語法，後面就可以直接針對CTE 的結果做操作。

所以透過CTE 可以原本需要子查詢的任務，變得比較好閱讀，意圖也較容易掌握。

另外，值得一提的是，官網文件中有說到，當批次中的陳述式使用CTE時，在CTE之前的

陳述式，後面必須接著分號；這就是為何習慣上都會在 WITH 前面加上「;」號隔開前段語法

如何實作遞迴？

首先，文章開頭也有提到的「可讓單一陳述式共享CTE資料」，官網亦有提到此特性：

官網對於實作遞迴的說明

• 遞迴 CTE 定義必須包含至少兩個 CTE 查詢定義，錨點成員和遞迴成員各一個。

  您可以定義多個錨點成員和遞迴成員；不過，所有錨點成員查詢定義都必須放在第一個遞迴成員定義的前面。

   除非 CTE 查詢定義參考 CTE 本身，否則，它們都是錨點成員。
  
  

• 錨點成員必須由下列其中一個設定運算子所組合：UNION ALL、UNION、INTERSECT 或 EXCEPT。

  在最後一個錨點成員和第一個遞迴成員之間，以及在組合多個成員時，UNION ALL 

  是唯一允許使用的設定運算子。
  
  

• 錨點和遞迴成員中的資料行數目必須相同。
  
  

• 遞迴成員資料行的資料類型必須與錨點成員中對應資料行的資料類型相同。
  
  

• 遞迴成員的 FROM 子句只能參考 CTE expression_name一次。

…

看範例

有個資料表MyEmployess，記載的各員工的基本資料與Title

今天需要將員工分等級，邏輯是某個員工他所屬的ManagerID 若還有上一個層級，那Level 就加1，

若沒有就停止，所以我們可以直接看出，EmployeeID 為0 的員工就是CEO，而這類需求

很適合用遞迴實作，以下看一下語法

以UNION ALL 為界，下方為Recursive Member，並且遞迴呼叫也只有Recursive Member 會

被重覆執行，直到查無資料為止。

我們以EmployeeID = 16 這筆資料來看，其ManagerID = 273 , 這邊level++，又EmployeeID = 273 的

ManagerID = 1 所以level++，而EmployeeID = 1 實則為CEO 沒有上一層了，所以中間

加了兩次故level =2；以此邏輯，下方是結果

設計遞迴時，因為資料有時無法一眼就看出會進入幾次，怕會有無窮迴圈的狀況，可加入

MAXRECURSION 設定最大遞迴次數，使用方式如下

因我們知道該範例最大層級為3，若設定為2就會出現提示，如下所示

其他更進階的使用可參考官方文件介紹搂