淺析sql server 公共表達式的簡單應用
一、前言
現(xiàn)在做項目數(shù)據(jù)訪問基本都會選擇一種orm框架,它以面向?qū)ο蟮男问狡帘蔚讓拥臄?shù)據(jù)訪問形式,讓開發(fā)人員更集中在業(yè)務處理上,而不是和數(shù)據(jù)庫的交互上,幫助我們提高開發(fā)效率;例如一些簡單的insert、update,我們不需要寫insert into...sql 語句,而是直接new一個實體對象,然后db.Insert(entity),看起來是那么清爽;像EF這樣比較完善的orm,支持linq語法對數(shù)據(jù)庫進行訪問,寫起來就更加爽了,有些人甚至認為開發(fā)人員可以不用會寫sql語句了...但現(xiàn)實不會讓你工作得那么輕松,作為開發(fā)人員對數(shù)據(jù)庫這一塊的學習還是很有必要的;且不說一些靈活性和效率問題,實際工作中用sql的地方還是非常多的,經(jīng)常在碼代碼的時候,突然就傳來領導的聲音,那個某某某,你趕緊給我出一份報表,那個誰誰誰,你趕緊給我出一份XXX的數(shù)據(jù)...很急。
二、使用CTE統(tǒng)計樹形結構
最近在碼代碼時,領導就來一句:嘿man,你給我統(tǒng)計一下所有xxx產(chǎn)品的信息,要快,那邊在催了...。這里抽象一下,如下,大概就是找出所有家具產(chǎn)品的信息,這個分類表包含樹形結構,ParentId為0是某種分類的根,它下面可能有許多種子節(jié)點/葉子節(jié)點。這里需要要找的實際就是一個以家具為根的樹?!?/p>
測試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)驗的朋友很快就知道怎么寫,而實際寫法也很簡單。知道這是樹形結構,在腦海里出現(xiàn)了:自鏈接查詢、子查詢、臨時表、游標、用程序?qū)懘a遞歸...公共表達式(CTE),OK!CTE的語法如下:
WITH CTE名稱[目標列] 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返回上一次的結果集。例如,初始化時,cte結果是ProductId 1,第一次遞歸時,會找到ParentId為1的產(chǎn)品,也就是3,4,并且與上一個結果集UNION ALL得到本次結果集返回,再遞歸時cte就是1,3,4了;而遞歸的結束條件就是本次查詢的結果為空集,此時遞歸結束,并返回最終結果集。
另外需要說的是,CTE是虛擬的,sql server會為它重新生成查詢語句,直接訪問底層對象;所以在一些性能要求較高的地方,還是要通過執(zhí)行計劃來判斷是否需要優(yōu)化,有時候方便是以性能為代價的。
以上就是本文的全部內(nèi)容,希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,同時也希望多多支持腳本之家!
相關文章
Ubuntu 17.10安裝phpMyAdmin數(shù)據(jù)庫管理工具配置詳解
這篇文章主要介紹了Ubuntu 17.10安裝phpMyAdmin數(shù)據(jù)庫管理工具,通過示例代碼給大家講解了安裝phpmyadmin數(shù)據(jù)庫的方法,非常不錯,具有一定的參考借鑒價值,需要的朋友可以參考下2018-03-03使用sqlplus創(chuàng)建DDL和DML操作技巧
這篇文章主要介紹了使用sqlplus創(chuàng)建DDL和DML操作技巧,非常不錯,具有參考借鑒價值,需要的朋友可以參考下2018-05-05比較SQL Server與Oracle、DB2數(shù)據(jù)庫的一些知識
本文比較大型數(shù)據(jù)庫SQL Server與Oracle、DB2的一些不同應用之處。對于以后開發(fā)選擇什么樣的數(shù)據(jù)庫。2010-03-03