SQLServer樹查詢

標籤：沒有   end   row   begin   nio   網路工程   inner   執行   動態   

       感覺這個CTE遞迴查詢蠻好用的，先舉個例子：

 use City;gocreate table Tree( ID int identity(1,1) primary key not null, Name varchar(20) not null, Parent varchar(20) null)goinsert 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(‘招生辦‘,‘大學‘)gowith 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 Levlefrom Tree inner join (select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,0 as Levle from Tree where Parent is null)as CTEon 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 Levlefrom Tree inner join(第二次遞迴查詢的SQL語句)as CTEon 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 Levlefrom Tree inner join(上次遞迴查詢的結果集，僅僅是上次那一次的，而不是以前的總和結果集)as CTEon 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 CTEorder by LTRIM(OrderID)--最後將這個整型資料轉換為字串型的進行排序--有時候整型可以比大小，字串也可以，字串比的大小是一位一位進行字元比較的--整型+字串==整型，只有字串+字串==兩個字串的並和--遞迴查詢中：第二條記錄可以引用第一條記錄的值--動態載入記錄時，同一個等級的記錄識別符：RowNumber()over(order by getdate())--延伸：可以動態擷取某個部門下的所以子部門。也可以擷取該部門上級的所以部門--總結：首先要拼湊出一個整型資料，然後轉換為字串，最後是進行字串的order，而不是整型資料的order，

最後的結果為：

圖2 運行結果

這樣，無論使用者插入多少條記錄都可以進行按部門，按規律進行查詢。

SQLServer樹查詢