原文:
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('大學',null)
- insert Tree values('學院','大學')
- insert Tree values('計算機學院','學院')
- insert Tree values('網絡工程','計算機學院')
- insert Tree values('信息管理','計算機學院')
- insert Tree values('電信學院','學院')
- insert Tree values('教務處','大學')
- insert Tree values('材料科','教務處')
- insert Tree values('招生辦','大學')
- 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.運行定位點成員，創建第一個調用或基準結果集 (T0)。
- --3.運行遞歸成員，將 Ti 作為輸入(這里只有一條記錄)，將 Ti+1 作為輸出。
- --4.重復步驟 3，直到返回空集。
- --5.返回結果集。這是對 T0 到 Tn 執行 UNION ALL 的結果。
上面的SQL語句再次插入一條數據：
insert Tree values('網絡1班','網絡工程')
運行結果如下圖：
圖1 運行結果
注意點：貌似在遞歸成員處所選擇的字段都必須Tree表的數據，而不能是CTE結果集中的除了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
獲取的結果為：
Name Parent TE Levle
-------------------------------------
大學 NULL 大學 0
遞歸第2次所獲取的結果集合的類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子查詢的結果就是第一次遞歸查詢的結果集，上面SQL運行結果為：
同樣的，將第二次遞歸查詢的上面三條記錄作為第三次查詢的‘定位成員’：
【這里要注意，上面的三條記錄是從最后一條開始依次作為第三次遞歸的輸入的，即第一條是ID=9的記錄，接下來是7和2，關于第四次遞歸也類似】
第三次遞歸類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
結果如下：
其實每次遞歸的類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
- (上次遞歸查詢的結果集，僅僅是上次那一次的，而不是以前的總和結果集)
- as CTE
- on Tree.Parent=CTE.Name
第四次遞歸一次類推，最后所查詢的結果為上面所有遞歸的union。
續：在上面的SQ語句查詢結果中，ID為10的記錄應該要放在ID為4的后面。
往數據表中再次添加兩條記錄：
insert Tree values('計科','計算機學院')
insert Tree values('我','網絡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
- --最關鍵是上面這個字段，要獲取排序字段，按字符串來排序。
- --其中窗口函數必須要使用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)--最后將這個整型數據轉換為字符串型的進行排序
-
- --有時候整型可以比大小，字符串也可以，字符串比的大小是一位一位進行字符比較的
- --整型+字符串==整型，只有字符串+字符串==兩個字符串的并和
- --遞歸查詢中：第二條記錄可以引用第一條記錄的值
- --動態加載記錄時，同一個等級的記錄識別符：RowNumber()over(order by getdate())
- --延伸：可以動態獲取某個部門下的所以子部門。也可以獲取該部門上級的所以部門
-
- --總結：首先要拼湊出一個整型數據，然后轉換為字符串，最后是進行字符串的order，而不是整型數據的order，
最后的結果為：
圖2 運行結果
這樣，無論用戶插入多少條記錄都可以進行按部門，按規律進行查詢。
posted on 2014-07-17 14:46
Ke 閱讀(748)
評論(0) 編輯 收藏 所屬分類:
sql server