公用表表達式簡介:
公用表表達式 (CTE) 可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執行范圍內定義的臨時結果集。CTE 與派生表類似,具體表現在不存儲為對象,並且只在查詢期間有效。與派生表的不同之處在於,公用表表達式 (CTE) 具有一個重要的優點,那就是能夠引用其自身,從而創建遞歸 CTE。遞歸 CTE 是一個重復執行初始 CTE 以返回數據子集直到獲取完整結果集的公用表表達式。
下面先創建一個表,並插入一些數據:
create table Role_CTE ( Id int not null, Name nvarchar(32) not null, ParentId int not null ) insert into Role_CTE(Id,Name,ParentId) select '1','超級管理員','0' union select '2','管理員A','1' union select '3','管理員B','2' union select '4','會員AA','2' union select '5','會員AB','2' union select '6','會員BA','3' union select '7','會員BB','3' union select '8','用戶AAA','4' union select '9','用戶BBA','7' -- 創建一個復合聚集索引 create clustered index Clu_Role_CTE_Index on Role_CTE(Id,ParentId) with ( pad_index=on, fillfactor=50, drop_existing=off, statistics_norecompute=on ) select * from Role_CTE
查找指定節點的所有子孫節點:
使用普通 sql 語句實現:
declare @level int declare @node int declare @ResTab table ( node int not null, lv int not null ) set @level=0 -- 表示初始的等級 set @node=3 --表示初始的節點ID,即從指定的哪個節點開始查找 insert into @ResTab -- 為表變量插入初始的數據 select Id,@level from Role_CTE where Id=@node while(@@ROWCOUNT>0) begin set @level=@level+1 insert into @ResTab select b.Id,@level from @ResTab a join Role_CTE b on a.node=b.ParentId and lv=@level-1 -- join 等於 inner join(內連接)和自連接 end select a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id
以上是根據指定節點ID(3),查找父節點ID(即字段 ParentId)等於指定的節點ID,如果有就插入,並繼續循環。
PS:lv=@level-1 是重點,不然會進入死循環,作用就是限制只插入一次。
如果需要限制循環的次數,即遞歸的層數,那麼只需要在 while 條件裡面添加一個限制即可。如下:
declare @level int declare @node int declare @num int declare @ResTab table ( node int not null, lv int not null ) set @level=0 -- 表示初始的等級 set @node=3 --表示初始的節點ID,即從指定的哪個節點開始查找 set @num=1 -- 指定遞歸層級,即循環的次數 insert into @ResTab -- 為表變量插入初始的數據 select Id,@level from Role_CTE where Id=@node while(@@ROWCOUNT>0 and @level<@num) begin set @level=@level+1 insert into @ResTab select b.Id,@level from @ResTab a join Role_CTE b on a.node=b.ParentId and lv=@level-1 -- join 等於 inner join(內連接)和自連接 end select a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id
當然,如果指定了循環次數,就可以不用 while 判斷語句的 @@rowcount>0 了。
使用 SQL CTE 實現:
declare @node int set @node=3; with temp_cte as ( select Id,Name,0 lv -- 查詢出“根節點”,即指定的起始節點 from Role_CTE where Id=@node union all select b.Id,b.Name,a.lv+1 from temp_cte a join Role_CTE b on a.Id=b.ParentId ) select * from temp_cte
使用 CTE 控制遞歸的層數,與上面類似。如下:
declare @node int declare @num int set @node=3; set @num=1; with temp_cte as ( select Id,Name,0 lv -- 查詢出“根節點”,即指定的起始節點 from Role_CTE where Id=@node union all select b.Id,b.Name,a.lv+1 from temp_cte a join Role_CTE b on a.Id=b.ParentId and a.lv<@num --控制遞歸層數 ) select * from temp_cte
查找指定節點的所有祖先節點:
使用普通 sql 語句實現:
declare @level int declare @node int declare @num int declare @ResTab table ( node int not null, lv int not null ) set @level=0 -- 表示初始的等級 set @node=8 --表示初始的節點ID,即從指定的哪個節點開始查找 set @num=2 -- 指定遞歸層級,即循環的次數 while(@level<=@num and @node is not null) -- 如果為空就表示沒有查到父級了 begin insert into @ResTab select @node,@level set @level=@level+1 select @node=ParentId from Role_CTE where Id=@node end select a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id
使用 SQL CTE 實現:
declare @node int declare @num int set @node=8; set @num=2; with temp_cte as ( select Id,Name,ParentId,0 lv -- 查詢出“根節點”,即指定的起始節點 from Role_CTE where Id=@node union all select b.Id,b.Name,b.ParentId,a.lv+1 from temp_cte a join Role_CTE b on a.ParentId=b.Id and a.lv < @num --控制遞歸層數 ) select * from temp_cte
以上所述是小編給大家介紹的SQL Server 公用表表達式(CTE)實現遞歸的方法,希望對大家有所幫助,如果大家有任何疑問歡迎給我留言,小編會及時回復大家的,在此也非常感謝大家對 網站的支持!