一、前言　　

　　現(xiàn)在做項(xiàng)目數(shù)據(jù)訪問基本都會選擇一種orm框架，它以面向?qū)ο蟮男问狡帘蔚讓拥臄?shù)據(jù)訪問形式，讓開發(fā)人員更集中在業(yè)務(wù)處理上，而不是和數(shù)據(jù)庫的交互上，幫助我們提高開發(fā)效率；例如一些簡單的insert、update，我們不需要寫insert into...sql 語句，而是直接new一個實(shí)體對象，然后db.Insert(entity)，看起來是那么清爽；像EF這樣比較完善的orm，支持linq語法對數(shù)據(jù)庫進(jìn)行訪問，寫起來就更加爽了，有些人甚至認(rèn)為開發(fā)人員可以不用會寫sql語句了...但現(xiàn)實(shí)不會讓你工作得那么輕松，作為開發(fā)人員對數(shù)據(jù)庫這一塊的學(xué)習(xí)還是很有必要的；且不說一些靈活性和效率問題，實(shí)際工作中用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é)點(diǎn)/葉子節(jié)點(diǎn)。這里需要要找的實(shí)際就是一個以家具為根的樹。　

測試sql語句：

DECLARE @Product TABLE(ProductId INT, ParentId INT, ProductName NVARCHAR(64))INSERT INTO @ProductVALUES(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,'裙子')

三、實(shí)現(xiàn)

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

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

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

;WITH cteAS(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的遞歸查詢主要包含兩個部分，定位點(diǎn)成員和遞歸成員。如上面的查詢，UNION ALL 前面的SELECT 就是定位點(diǎn)成員，它是查詢的初始化；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í)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，同時也希望多多支持武林網(wǎng)！