sql server 公共表達式的簡單應用（cte）

一、前言　　

　　現在做項目數據訪問基本都會選擇一種orm框架，它以面向對象的形式屏蔽底層的數據訪問形式，讓開發人員更集中在業務處理上，而不是和資料庫的交互上，幫助我們提高開發效率；例如一些簡單的insert、update，我們不需要寫insert into...sql 語句，而是直接new一個實體對象，然後db.Insert(entity)，看起來是那麼清爽；像EF這樣比較完善的orm，支持linq語法對資料庫進行訪問，寫起來就更加爽了，有些人甚至認為開發人員可以不用會寫sql語句了...但現實不會讓你工作得那麼輕鬆，作為開發人員對資料庫這一塊的學習還是很有必要的；且不說一些靈活性和效率問題，實際工作中用sql的地方還是非常多的，經常在碼代碼的時候，突然就傳來領導的聲音，那個某某某，你趕緊給我出一份報表，那個誰誰誰，你趕緊給我出一份XXX的數據...很急。

二、使用CTE統計樹形結構

　　最近在碼代碼時，領導就來一句：嘿man，你給我統計一下所有xxx產品的信息，要快，那邊在催了...。這裡抽象一下，如下，大概就是找出所有傢具產品的信息，這個分類表包含樹形結構，ParentId為0是某種分類的根，它下麵可能有許多種子節點/葉子節點。這裡需要要找的實際就是一個以傢具為根的樹。

測試sql語句：

DECLARE @Product TABLE
(ProductId INT,
 ParentId INT,
 ProductName NVARCHAR(64))

INSERT INTO @Product
VALUES
(1,0,'傢具'),
(2,0,'服裝'),
(3,1,'大型傢具'),
(4,1,'小型傢具'),
(5,2,'男裝'),
(6,2,'女裝'),
(7,3,'床'),
(8,3,'衣櫃'),
(9,3,'沙發'),
(10,4,'電腦桌'),
(11,4,'椅子'),
(12,5,'牛仔褲'),
(13,5,'襯衫'),
(14,6,'裙子')

三、實現

　　這種需求實際很多，有經驗的朋友很快就知道怎麼寫，而實際寫法也很簡單。知道這是樹形結構，在腦海裡出現了：自鏈接查詢、子查詢、臨時表、游標、用程式寫代碼遞歸...公共表達式(CTE)，OK！CTE的語法如下：

WITH CTE名稱[目標列]
AS
(
<定義CTE的內部查詢>
)
<對CTE進行查詢的外部查詢>

　　具體來說，CTE屬於表表達式，另一種表表達式是派生表（子查詢），有時候使用CTE可以優化我們的代碼，使我們的代碼更加簡單、易讀。而且CTE支持遞歸查詢，上面的需求寫法為：

;WITH cte
AS
(SELECT * FROM @Product
  WHERE ProductId = 1
 UNION ALL  
 SELECT p.* FROM @Product p
  INNER JOIN cte t ON p.ParentId = t.ProductId
)
SELECT*FROM cte
 ORDER BY ProductId

四、解析

　　CTE的遞歸查詢主要包含兩個部分，定位點成員和遞歸成員。如上面的查詢，UNION ALL 前面的SELECT 就是定位點成員，它是查詢的初始化；UNION ALL下麵的屬於遞歸成員，我們可以遞歸查詢時，每次都為CTE返回上一次的結果集。例如，初始化時，cte結果是ProductId 1，第一次遞歸時，會找到ParentId為1的產品，也就是3，4，並且與上一個結果集UNION ALL得到本次結果集返回，再遞歸時cte就是1,3,4了；而遞歸的結束條件就是本次查詢的結果為空集，此時遞歸結束，並返回最終結果集。

　　另外需要說的是，CTE是虛擬的，sql server會為它重新生成查詢語句，直接訪問底層對象；所以在一些性能要求較高的地方，還是要通過執行計劃來判斷是否需要優化，有時候方便是以性能為代價的。