淺析sql server 公共表達(dá)式的簡單應(yīng)用

一、前言　　

　　現(xiàn)在做項(xiàng)目數(shù)據(jù)訪問基本都會選擇一種orm框架，它以面向?qū)ο蟮男问狡帘蔚讓拥臄?shù)據(jù)訪問形式，讓開發(fā)人員更集中在業(yè)務(wù)處理上，而不是和數(shù)據(jù)庫的交互上，幫助我們提高開發(fā)效率；例如一些簡單的insert、update，我們不需要寫insert into...sql 語句，而是直接new一個(gè)實(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)常在碼代碼的時(shí)候，突然就傳來領(lǐng)導(dǎo)的聲音，那個(gè)某某某，你趕緊給我出一份報(bào)表，那個(gè)誰誰誰，你趕緊給我出一份XXX的數(shù)據(jù)...很急。

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

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

測試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,'裙子')

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

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

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

具體來說，CTE屬于表表達(dá)式，另一種表表達(dá)式是派生表（子查詢），有時(shí)候使用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的遞歸查詢主要包含兩個(gè)部分，定位點(diǎn)成員和遞歸成員。如上面的查詢，UNION ALL 前面的SELECT 就是定位點(diǎn)成員，它是查詢的初始化；UNION ALL下面的屬于遞歸成員，我們可以遞歸查詢時(shí)，每次都為CTE返回上一次的結(jié)果集。例如，初始化時(shí)，cte結(jié)果是ProductId 1，第一次遞歸時(shí)，會找到ParentId為1的產(chǎn)品，也就是3，4，并且與上一個(gè)結(jié)果集UNION ALL得到本次結(jié)果集返回，再遞歸時(shí)cte就是1,3,4了；而遞歸的結(jié)束條件就是本次查詢的結(jié)果為空集，此時(shí)遞歸結(jié)束，并返回最終結(jié)果集。

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

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