SQL 2005 T-SQL Enhancement: Common Table Expression
4 |
從SQL 2000時代,相信不少人就知道運用Derived Table的技巧,將一段特定條件的查詢結果當作一個資料表來處理,讓整個查詢語句變得簡潔,且不必付出操弄暫存資料表的額外成本。不過,Derived Table與暫存資料表間還是有些差別,暫存資料表在建立後,可以反覆查詢,而Derived Table卻不行。CTE的出現打破了這項限制,兼具了Derived Table簡便輕巧的特性,也具備了如暫存資料表般可重覆使用的優點。
不過,改善了Derived Table無法重覆使用的缺點並不是CTE最受人矚目的突破,可以支援遞迴式查詢才是其最犀利的強項。這項特性很適合運用在複雜的階層式架構中,例如: 組織圖、BOM表…等等。
我們用一個PC組裝的零件清單作例子,假設組裝廠是依如上表的零件組合成模組、再組裝成PC,則我們可以用CTE的遞迴查詢技巧直接在T-SQL中建出整個關聯架構(程式如下)。
** CTE範例 **
--如果CTE的WITH不在第一列, 前方要加上;
;WITH DIYParts_BOM(PartName, Parent, Level, SortCol)
AS
(
--Recursive CTE分為兩個部分, 第一部分為Anchor Member
--指不會被遞迴呼叫到的部分
SELECT PartName, Parent, 0, CONVERT(nvarchar(128),PartNo)
FROM DIYParts WHERE Parent=N'ROOT'
UNION ALL
--UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,
--直到無任何查詢結果為止
SELECT P.PartName, P.Parent, B.Level+1,
CONVERT(nvarchar(128), B.SortCol+'-'+CONVERT(nvarchar(128),P.PartNo))
FROM DIYParts P, DIYParts_BOM B
WHERE P.Parent=B.PartName
)
SELECT REPLICATE(' ',Level) + PartName, Level, SortCol
FROM DIYParts_BOM ORDER BY SortCol
遞迴式CTE內部分為兩個部分,以UNION ALL為界,前方的稱之為Anchor Member,後方的則為Recursive Member,遞迴呼叫時只有Recursive Member會重覆執行,一直執行到查無資料為止。當然,既然是遞迴,就要留意無窮迴圈的問題,最好能在事前防範,否則有個MAXRECURION參數可以設定最多的遞迴層數,超出時就產生錯誤。
Comments
# by Eric
很清晰易懂的範例
# by steve
今天在Oracle上找到可以遞迴查詢的語法 用版主的範例來寫就是 SELECT PartName,Parent,Level FROM DIYParts START WITH Parent='ROOT' CONNECT BY PRIOR PartName = Parent 這真是太好玩啦
# by Jeffrey
驚! 查了一下,這把戲似乎Oracle十多年前就有了。易而善胃腸藥,原來華陀早知道... orz http://tinyurl.com/3542ol
# by steve
更棒的是 連Error Handling都有了 資料裡如果會造成無窮迴圈時,Oracle會丟一個錯誤訊息出來,不給執行 以前搞得半死的循環代理問題應該可以用這個方法解決吧 begin transaction insert 代理資料 select start with connect by 檢查是否造成迴圈 若有則rollback 否則commit 喔喔喔喔......這在SQL SERVER要花多少精神檢查阿.... 問題是....我現在又不寫Workflow系統.....orz....