一、前言　　

　　現(xiàn)在做項目數(shù)據(jù)訪問基本都會選擇一種orm框架，它以面向?qū)ο蟮男问狡帘蔚讓拥臄?shù)據(jù)訪問形式，讓開發(fā)人員更集中在業(yè)務(wù)處理上，而不是和數(shù)據(jù)庫的交互上，幫助我們提高開發(fā)效率；例如一些簡單的insert、update，我們不需要寫insert into...sql 語句，而是直接new一個實體對象，然后db.Insert(entity)，看起來是那么清爽；像EF這樣比較完善的orm，支持linq語法對數(shù)據(jù)庫進行訪問，寫起來就更加爽了，有些人甚至認(rèn)為開發(fā)人員可以不用會寫sql語句了...但現(xiàn)實不會讓你工作得那么輕松，作為開發(fā)人員對數(shù)據(jù)庫這一塊的學(xué)習(xí)還是很有必要的；且不說一些靈活性和效率問題，實際工作中用sql的地方還是非常多的，經(jīng)常在碼代碼的時候，突然就傳來領(lǐng)導(dǎo)的聲音，那個某某某，你趕緊給我出一份報表，那個誰誰誰，你趕緊給我出一份XXX的數(shù)據(jù)...很急。

二、使用CTE統(tǒng)計樹形結(jié)構(gòu)

　　最近在碼代碼時，領(lǐng)導(dǎo)就來一句：嘿man，你給我統(tǒng)計一下所有xxx產(chǎn)品的信息，要快，那邊在催了...。這里抽象一下，如下，大概就是找出所有家具產(chǎn)品的信息，這個分類表包含樹形結(jié)構(gòu)，ParentId為0是某種分類的根，它下面可能有許多種子節(jié)點/葉子節(jié)點。這里需要要找的實際就是一個以家具為根的樹。　

測試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,'沙發(fā)'),
(10,4,'電腦桌'),
(11,4,'椅子'),
(12,5,'牛仔褲'),
(13,5,'襯衫'),
(14,6,'裙子')

三、實現(xiàn)

這種需求實際很多，有經(jīng)驗的朋友很快就知道怎么寫，而實際寫法也很簡單。知道這是樹形結(jié)構(gòu)，在腦海里出現(xiàn)了：自鏈接查詢、子查詢、臨時表、游標(biāo)、用程序?qū)懘a遞歸...公共表達式(CTE)，OK！CTE的語法如下：

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

具體來說，CTE屬于表表達式，另一種表表達式是派生表（子查詢），有時候使用CTE可以優(yōu)化我們的代碼，使我們的代碼更加簡單、易讀。而且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返回上一次的結(jié)果集。例如，初始化時，cte結(jié)果是ProductId 1，第一次遞歸時，會找到ParentId為1的產(chǎn)品，也就是3，4，并且與上一個結(jié)果集UNION ALL得到本次結(jié)果集返回，再遞歸時cte就是1,3,4了；而遞歸的結(jié)束條件就是本次查詢的結(jié)果為空集，此時遞歸結(jié)束，并返回最終結(jié)果集。

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

以上就是本文的全部內(nèi)容，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，同時也希望多多支持腳本之家！

最新評論