標籤：style blog http io ar os 使用 sp 資料
感覺這個CTE遞迴查詢蠻好用的，先舉個例子：
[c-sharp] view plain copy print ?
- 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語句：
[c-sharp] view plain copy print ?
- 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語句為：
[c-sharp] view plain copy print ?
- 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語句
[c-sharp] view plain copy print ?
- 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可為如下所示：
[c-sharp] view plain copy print ?
- 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語句：
[c-sharp] view plain copy print ?
- 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 運行結果
這樣，無論使用者插入多少條記錄都可以進行按部門，按規律進行查詢。
SQL Server 2005中的CTE遞迴查詢得到一棵樹
