sql-公用表表達式（CTE）

定義

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

非遞歸公用表表達式

非遞歸公用表表達式是查詢結果僅僅一次性返回一個結果集用於外部查詢調用。並不在其定義的語句中調用其自身的CTE。非遞歸公用表表達式的使用方式和視圖以及子查詢一致。

比如一個簡單的非遞歸公用表表達式

遞歸公用表表達式

遞歸公用表表達式指的是在CTE內的語句中調用其自身的CTE。

對於遞歸公用表達式來說，實現原理也是相同的，同樣需要在語句中定義兩部分：

•    基本語句
•    遞歸語句

在SQL這兩部分通過UNION ALL連接結果集進行返回:
比如：

先建一張表欄目表如下，欄目Id，欄目名稱，欄目的父欄目。

現在使用CTE查詢其每個欄目是第幾層欄目的代碼如下：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本語句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --遞歸語句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 　　--遞歸調用
    ON c.ParentId = ce.Id
)

SELECT * FROM COL_CTE

輸出結果如下：

　　0表示頂級欄目。1就是1級欄目。語法非常優雅。就一個SELECT * FRON COL_CTE。這正是CTE強大的地方，但是，這要有約束，否則如果無限制遞歸可以會消耗掉非常多的系統資源。還可以通過OPTION(MAXRECURSION n)　限制遞歸的最大次數。

　如將上面的查詢語法改為：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本語句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --遞歸語句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 
    ON c.ParentId = ce.Id
)

SELECT * FROM COL_CTE
OPTION(MAXRECURSION 2)　　--指定最大遞歸次數為2

使用時註意點

with開始前有時候建議加';'符號結束前面的語句。因為with在其他地方有他的含義，加以區別。

總結 

    CTE是一種十分優雅的存在。CTE所帶來最大的好處是代碼可讀性的提升,這是良好代碼的必須品質之一。使用遞歸CTE可以更加輕鬆愉快的用優雅簡潔的方式實現複雜的查詢。