一、前言
現在做項目數據訪問基本都會選擇一種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會為它重新生成查詢語句，直接訪問底層對象；所以在一些性能要求較高的地方，還是要通過執行計劃來判斷是否需要優化，有時候方便是以性能為代價的。
以上就是本文的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，同時也希望多多支持！
