大家好，今天我們來學習一下公用表表達式。在項目中需要編寫SQL語句，因為自己本身對SQL Server知之甚少，一些較復雜的SQL語句，我是寫不出來的。于是，請教我的一位好朋友，數據庫MVP。他給我發來了一段SQL語句，相當好用。在佩服他的SQL語句時，我發現了公用表表達式這個概念，于是就Bing了一篇文章，用心研究了一番。在這里與大家分享一下我的學習心得。

公用表表達式(Common Table ExPRessions)

CTE（公用表表達式）的作用類似于我們的臨時表，就是可以作為SELECT、CREATE、UPDATE等語句中的一部分。它也可以簡化我們的語句，提高數據庫操作性能。就像我上一篇講到的SQL分頁查詢:

--------假設我們有一個100W條數據的訂單表，需要分頁查詢。 DECLARE @RowNumber AS INT, @PageNumber AS INTSET @RowNumber=5SET @PageNumber=2;WITH OrderedOrders AS(      SELECT  ROW_NUMBER() OVER(ORDER BY OrderDate) AS RowNumber        *  FROM Sales.SalesOrderHeader)SELECT * FROM OrderOrders  WHERERowNumber BETEEWN  ((@PageNumber-1)*@RowNumber)+1) AND (@PageNumber*@RowNumber)

在這里，我們是把已經每列都生成了標識后的數據，放入了CTE中，以充當下面的SELECT 語句的一部分(數據源)。

這是CTE的基本語法

expression_name：公共表表達式的名字

[(column_name[,…n])]:這個是查詢字段列表，需要查詢的字段。(當要查詢的字段匹配數據源中所有的列時，這個列表可以省略，默認查詢全部列)

(CTE_query_definition):我們要查詢的SQL語句

使用公共表表達式遞歸查詢(Recursive Queries Using Common Table Expressions)

如果你認為CTE只有簡簡單單的臨時表功能的話，那你就太小看它了。它其實還有一個非常實用、非常有意義的功能。遞歸函數，大家應該了解過。就是根據某個條件來判斷，進行自我調用。使用Recursive CTE(遞歸公共表表達式)進行查詢，與一般的遞歸函數原理是一樣的。說白了，就是CTE引用CTE查詢到的結果。上例子吧:

這段代碼的作用，是要查詢出員工信息。信息包括，員工的上級領導編號，員工自身編號，員工職位，員工所在部門編號、員工等級。研究一下這段代碼：

我們把這段代碼分為四部分，第一部分Anchor member definition,第二部分Recursive member definition,第三部分Statement that executes the CTE.

Anchor member definition:不知道應該怎樣翻譯它，它的作用就像是一個調用函數，它觸發遞歸查詢。

Recursive member definition:它的作用就像是一個遞歸函數，在這里，我們把Anchor member definition查詢到的結果當作參數 ，來查詢Recursive member definition.將Recursive member definition查詢到的結果當作參數，繼續查詢Recursive member definition,直道沒有結果返回。

Statement that executes the CTE: 外部調用CTE的語句。

我們一步一步來執行一下這段代碼: Anchor member definition，會查詢出來等級最高的員工，他沒有上級領導。結果如下:

Recursive member definition 通過 Anchor member definition返回的結果作為參數，根據e.ManagerID=d.Employee條件來查詢。因為Anchor member definition返回的結果是EmployeeID為1，所以Recursive member definition 會去查詢Mananger=1的數據。得到的結果如下:

接著，會拿這個結果當作參數，繼續查詢。這次回去查詢Manager=273的數據，得到結果如下圖:

繼續拿這個結果作參數，繼續查詢。這次會去查詢Manager IN (16,274,285)的數據，得到結果如下:

我們執行查詢CTE 會得到如下結果:

上圖畫紅線的數據，是Anchor member definition 的數據，我們通過UNION ALL 將它與Recursive member definition 連接。通過這個一步步查詢，我們可以發現，Anchor member definition 只是提夠了一次數據，Recursive member definition是遞歸執行者。

Recursive CTE,也可以起到優化語句的效果。我們完全可以通過遞歸查詢，把那些不得不執行多次的相同語句，簡化成一條遞歸查詢即可。

原文地址:http://technet.microsoft.com/en-us/library/ms186243(v=SQL.105).aspx