SQL Server中怎么利用公用表表達式實現遞歸

這篇文章給大家介紹SQL Server中怎么利用公用表表達式實現遞歸，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

公用表表達式簡介：

公用表表達式 (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_Indexon Role_CTE(Id,ParentId)with( pad_index=on, fillfactor=50, drop_existing=off, statistics_norecompute=on)select * from Role_CTE

查找指定節點的所有子孫節點：

使用普通 sql 語句實現：

declare @level intdeclare @node intdeclare @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=@nodewhile(@@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（內連接）和自連接endselect 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 intdeclare @node intdeclare @num intdeclare @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=@nodewhile(@@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（內連接）和自連接endselect 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_cteas( 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 intset @node=3;set @num=1;with temp_cteas( 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 intdeclare @node intdeclare @num intdeclare @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=@nodeendselect 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 intset @node=8;set @num=2;with temp_cteas( 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中怎么利用公用表表達式實現遞歸就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。