原文:
http://blog.csdn.net/bin_520_yan/article/details/5998349
感覺這個CTE遞歸查詢蠻好用的，先舉個例子：
- use City;
- go
- create table Tree
- (
- ID int identity(1,1) primary key not null,
- Name varchar(20) not null,
- Parent varchar(20) null
- )
- go
- insert Tree values('大學(xué)',null)
- insert Tree values('學(xué)院','大學(xué)')
- insert Tree values('計算機學(xué)院','學(xué)院')
- insert Tree values('網(wǎng)絡(luò)工程','計算機學(xué)院')
- insert Tree values('信息管理','計算機學(xué)院')
- insert Tree values('電信學(xué)院','學(xué)院')
- insert Tree values('教務(wù)處','大學(xué)')
- insert Tree values('材料科','教務(wù)處')
- insert Tree values('招生辦','大學(xué)')
- go
- with CTE as
- (
- -->Begin 一個定位點成員
- select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,0 as Levle from Tree where Parent is null
- -->End
- union all
- -->Begin一個遞歸成員
- select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle
- from Tree inner join CTE
- on Tree.Parent=CTE.Name
- -->End
- )
- select * from CTE order by ID
- --1.將 CTE 表達式拆分為定位點成員和遞歸成員。
- --2.運行定位點成員，創(chuàng)建第一個調(diào)用或基準結(jié)果集 (T0)。
- --3.運行遞歸成員，將 Ti 作為輸入(這里只有一條記錄)，將 Ti+1 作為輸出。
- --4.重復(fù)步驟 3，直到返回空集。
- --5.返回結(jié)果集。這是對 T0 到 Tn 執(zhí)行 UNION ALL 的結(jié)果。
上面的SQL語句再次插入一條數(shù)據(jù)：
insert Tree values('網(wǎng)絡(luò)1班','網(wǎng)絡(luò)工程')
運行結(jié)果如下圖：
圖1 運行結(jié)果
注意點：貌似在遞歸成員處所選擇的字段都必須Tree表的數(shù)據(jù)，而不能是CTE結(jié)果集中的除了Tree中沒有而CTE中有的字段在這里才可以引用，比如字段TE。
首先看下，遍歷的第1條記錄的SQL語句：
- select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,0 as Levle from Tree where Parent is null
獲取的結(jié)果為：
Name Parent TE Levle
-------------------------------------
大學(xué) NULL 大學(xué) 0
遞歸第2次所獲取的結(jié)果集合的類SQL語句為：
- select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle
- from Tree inner join
- (select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,0 as Levle from Tree where Parent is null)
- as CTE
- on Tree.Parent=CTE.Name
上面的CTE子查詢的結(jié)果就是第一次遞歸查詢的結(jié)果集，上面SQL運行結(jié)果為：
同樣的，將第二次遞歸查詢的上面三條記錄作為第三次查詢的‘定位成員’：
【這里要注意，上面的三條記錄是從最后一條開始依次作為第三次遞歸的輸入的，即第一條是ID=9的記錄，接下來是7和2，關(guān)于第四次遞歸也類似】
第三次遞歸類SQL語句
- select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle
- from Tree inner join
- (第二次遞歸查詢的SQL語句)as CTE
- on Tree.Parent=CTE.Name
結(jié)果如下：
其實每次遞歸的類SQL可為如下所示：
- select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle
- from Tree inner join
- (上次遞歸查詢的結(jié)果集，僅僅是上次那一次的，而不是以前的總和結(jié)果集)
- as CTE
- on Tree.Parent=CTE.Name
第四次遞歸一次類推，最后所查詢的結(jié)果為上面所有遞歸的union。
續(xù)：在上面的SQ語句查詢結(jié)果中，ID為10的記錄應(yīng)該要放在ID為4的后面。
往數(shù)據(jù)表中再次添加兩條記錄：
insert Tree values('計科','計算機學(xué)院')
insert Tree values('我','網(wǎng)絡(luò)1班')
再次修改上面的SQL語句：
- with CTE as
- (
- -->Begin 一個定位點成員
- select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,
- ROW_NUMBER()over(order by getdate()) as OrderID
- --最關(guān)鍵是上面這個字段，要獲取排序字段，按字符串來排序。
- --其中窗口函數(shù)必須要使用order by，但是不能用整型，那就用時間吧
- from Tree where Parent is null
- -->End
- union all
- -->Begin一個遞歸成員
- select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,
- CTE.OrderID*100+ROW_NUMBER()over(Order by GETDATE()) as OrderID
- from Tree inner join CTE
- on Tree.Parent=CTE.Name
- -->End
- )
- select * from CTE
- order by LTRIM(OrderID)--最后將這個整型數(shù)據(jù)轉(zhuǎn)換為字符串型的進行排序
-
- --有時候整型可以比大小，字符串也可以，字符串比的大小是一位一位進行字符比較的
- --整型+字符串==整型，只有字符串+字符串==兩個字符串的并和
- --遞歸查詢中：第二條記錄可以引用第一條記錄的值
- --動態(tài)加載記錄時，同一個等級的記錄識別符：RowNumber()over(order by getdate())
- --延伸：可以動態(tài)獲取某個部門下的所以子部門。也可以獲取該部門上級的所以部門
-
- --總結(jié)：首先要拼湊出一個整型數(shù)據(jù)，然后轉(zhuǎn)換為字符串，最后是進行字符串的order，而不是整型數(shù)據(jù)的order，
最后的結(jié)果為：
圖2 運行結(jié)果
這樣，無論用戶插入多少條記錄都可以進行按部門，按規(guī)律進行查詢。
posted on 2014-07-17 14:46
Ke 閱讀(748)
評論(0) 編輯 收藏 所屬分類:
sql server