SQLSERVER使用CTE

1.什麼是CTE

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

2.CTE優點 
      使用 CTE 可以獲得提高可讀性和輕鬆維護複雜查詢的優點。

        查詢可以分為單獨塊、簡單塊、邏輯產生塊。之後，這些簡單塊可用於產生更複雜的臨時 CTE，直到產生最終結果集。
3.CTE可使用的範圍      可以在使用者定義的常式（如函數、預存程序、觸發器或視圖）中定義 CTE。
4.CTE的文法

[WITH <common_table_expression> [ ,...n ]]<common_table_expression>::=        expression_name [( column_name [ ,...n ] )]    AS        (CTE_query_definition)

其中參數：

expression_name

通用資料表運算式的有效標識符。 expression_name 必須與在同一 WITH <common_table_expression>子句中定義的任何其他通用資料表運算式的名稱不同，但 expression_name 可以與基表或基視圖的名稱相同。在查詢中對 expression_name 的任何引用都會使用通用資料表運算式，而不使用基底物件。

column_name

在通用資料表運算式中指定列名。在一個 CTE 定義中不允許出現重複的名稱。指定的列名數必須與CTE_query_definition 結果集中列數匹配。只有在查詢定義中為所有結果列都提供了不同的名稱時，列名稱列表才是可選的。

CTE_query_definition

指定一個其結果集填充通用資料表運算式的 SELECT 語句。除了 CTE 不能定義另一個 CTE 以外，CTE_query_definition的 SELECT 語句必須滿足與建立視圖時相同的要求。

5.定義和使用CTE

（1）應用於非遞迴 CTE

CTE 之後必須跟隨引用部分或全部 CTE 列的 SELECT、INSERT、UPDATE 或 DELETE 語句。也可以在 CREATE VIEW 語句中將 CTE 指定為視圖中 SELECT 定義語句的一部分。可以在非遞迴 CTE 中定義多個 CTE 查詢定義。定義必須與以下集合運算子之一結合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。CTE 可以引用自身，也可以引用在同一 WITH
子句中預先定義的 CTE。不允許前向引用。不允許在一個 CTE 中指定多個 WITH 子句。例如，如果 CTE_query_definition 包含一個子查詢，則該子查詢不能包括定義另一個 CTE 的嵌套的 WITH 子句。不能在 CTE_query_definition 中使用以下子句：

COMPUTE 或 COMPUTE BY

   ORDERBY（除非指定了 TOP 子句）

   INTO

  帶有查詢提示的 OPTION 子句

   FORXML

   FORBROWSE

   如果將 CTE 用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾。

   可以使用引用 CTE 的查詢來定義遊標。

   可以在 CTE 中引用遠程伺服器中的表。

   在執行 CTE 時，任何引用 CTE 的提示都可能與該 CTE 訪問其基礎資料表時發現的其他提示相衝突，這種衝突與引用查詢中的視圖的提示所發生的衝突相同。發生這種情況時，查詢將返回錯誤。

（2）定義和使用遞迴 CTE

a. 定義遞迴 CTE

錨點成員必須與以下集合運算子之一結合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最後一個錨點成員和第一個遞迴成員之間，以及組合多個遞迴成員時，只能使用 UNION ALL 集合運算子。遞迴 CTE 定義至少必須包含兩個 CTE 查詢定義，一個錨點成員和一個遞迴成員。可以定義多個錨點成員和遞迴成員；但必須將所有錨點成員查詢定義置於第一個遞迴成員定義之前。所有
CTE 查詢定義都是錨點成員，但它們引用 CTE 本身時除外。錨點成員和遞迴成員中的列數必須一致。遞迴成員中列的資料類型必須與錨點成員中相應列的資料類型一致。遞迴成員的 FROM 子句只能引用一次 CTEexpression_name。在遞迴成員的
CTE_query_definition 中不允許出現下列項：

   SELECT DISTINCT

   GROUPBY

   HAVING

   標量彙總

   TOP

   LEFT、RIGHT、OUTER JOIN（允許出現 INNER JOIN）

   子查詢

   應用於對 CTE_query_definition 中的 CTE 的遞迴引用的提示。

b. 使用遞迴 CTE

   無論參與的 SELECT 語句返回的列的為空白性如何，遞迴 CTE 返回的全部列都可以為空白。

   如果遞迴 CTE 組合不正確，可能會導致無限迴圈。例如，如果遞迴成員查詢定義對父列和子列返回相同的值，則會造成無限迴圈。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中的一個 0 到 32,767 之間的值，來限制特定語句所允許的遞迴級數，以防止出現無限迴圈。這樣就能夠在解決產生迴圈的代碼問題之前控制語句的執行。伺服器範圍內的預設值是
100。如果指定 0，則沒有限制。每一個語句只能指定一個 MAXRECURSION 值。

   不能使用包含遞迴通用資料表運算式的視圖來更新資料。

   可以使用 CTE 在查詢上定義遊標。遞迴 CTE 只允許使用快速順向資料指標和靜態（快照）遊標。如果在遞迴 CTE 中指定了其他遊標類型，則該類型將轉換為靜態資料指標類型。

   可以在 CTE 中引用遠程伺服器中的表。如果在 CTE 的遞迴成員中引用了遠程伺服器，那麼將為每個遠端資料表建立一個假離線，這樣就可以在本地反覆訪問這些表。

如果定義了多個 CTE_query_definition，則這些查詢定義必須用下列一個集合運算子聯結起來：UNION ALL、UNION、EXCEPT 或 INTERSECT。