共計(jì) 3457 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。
這篇文章給大家介紹 SQL Server 中怎么利用公用表表達(dá)式實(shí)現(xiàn)遞歸，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。
公用表表達(dá)式簡(jiǎn)介：
公用表表達(dá)式 (CTE) 可以認(rèn)為是在單個(gè) SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執(zhí)行范圍內(nèi)定義的臨時(shí)結(jié)果集。CTE 與派生表類似，具體表現(xiàn)在不存儲(chǔ)為對(duì)象，并且只在查詢期間有效。與派生表的不同之處在于，公用表表達(dá)式 (CTE) 具有一個(gè)重要的優(yōu)點(diǎn)，那就是能夠引用其自身，從而創(chuàng)建遞歸 CTE。遞歸 CTE 是一個(gè)重復(fù)執(zhí)行初始 CTE 以返回?cái)?shù)據(jù)子集直到獲取完整結(jié)果集的公用表表達(dá)式。
下面先創(chuàng)建一個(gè)表，并插入一些數(shù)據(jù)：
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 , 超級(jí)管理員 , 0 union select 2 , 管理員 A , 1 union select 3 , 管理員 B , 2 union select 4 , 會(huì)員 AA , 2 union select 5 , 會(huì)員 AB , 2 union select 6 , 會(huì)員 BA , 3 union select 7 , 會(huì)員 BB , 3 union select 8 , 用戶 AAA , 4 union select 9 , 用戶 BBA , 7 -- 創(chuàng)建一個(gè)復(fù)合聚集索引 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查找指定節(jié)點(diǎn)的所有子孫節(jié)點(diǎn)：
使用普通 sql 語句實(shí)現(xiàn)：
declare @level intdeclare @node intdeclare @ResTab table( node int not null, lv int not null )set @level=0 -- 表示初始的等級(jí) set @node=3 -- 表示初始的節(jié)點(diǎn) ID, 即從指定的哪個(gè)節(jié)點(diǎn)開始查找 insert into @ResTab -- 為表變量插入初始的數(shù)據(jù) 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（內(nèi)連接）和自連接 endselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id以上是根據(jù)指定節(jié)點(diǎn) ID(3)，查找父節(jié)點(diǎn) ID(即字段 ParentId) 等于指定的節(jié)點(diǎn) ID，如果有就插入，并繼續(xù)循環(huán)。
PS：lv=@level-1 是重點(diǎn)，不然會(huì)進(jìn)入死循環(huán)，作用就是限制只插入一次。
如果需要限制循環(huán)的次數(shù)，即遞歸的層數(shù)，那么只需要在 while 條件里面添加一個(gè)限制即可。如下：
declare @level intdeclare @node intdeclare @num intdeclare @ResTab table( node int not null, lv int not null )set @level=0 -- 表示初始的等級(jí) set @node=3 -- 表示初始的節(jié)點(diǎn) ID, 即從指定的哪個(gè)節(jié)點(diǎn)開始查找 set @num=1 -- 指定遞歸層級(jí)，即循環(huán)的次數(shù) insert into @ResTab -- 為表變量插入初始的數(shù)據(jù) 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（內(nèi)連接）和自連接 endselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id當(dāng)然，如果指定了循環(huán)次數(shù)，就可以不用 while 判斷語句的 @@rowcount 0 了。
使用 SQL CTE 實(shí)現(xiàn)：
declare @node int set @node=3;with temp_cteas( select Id,Name,0 lv -- 查詢出“根節(jié)點(diǎn)”，即指定的起始節(jié)點(diǎn) 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 控制遞歸的層數(shù)，與上面類似。如下：
declare @node int declare @num intset @node=3;set @num=1;with temp_cteas( select Id,Name,0 lv -- 查詢出“根節(jié)點(diǎn)”，即指定的起始節(jié)點(diǎn) 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 -- 控制遞歸層數(shù))select * from temp_cte查找指定節(jié)點(diǎn)的所有祖先節(jié)點(diǎn)：
使用普通 sql 語句實(shí)現(xiàn)：
declare @level intdeclare @node intdeclare @num intdeclare @ResTab table( node int not null, lv int not null )set @level=0 -- 表示初始的等級(jí) set @node=8 -- 表示初始的節(jié)點(diǎn) ID, 即從指定的哪個(gè)節(jié)點(diǎn)開始查找 set @num=2 -- 指定遞歸層級(jí)，即循環(huán)的次數(shù) while(@level =@num and @node is not null) -- 如果為空就表示沒有查到父級(jí)了 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 實(shí)現(xiàn)：
declare @node int declare @num intset @node=8;set @num=2;with temp_cteas( select Id,Name,ParentId,0 lv -- 查詢出“根節(jié)點(diǎn)”，即指定的起始節(jié)點(diǎn) 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 -- 控制遞歸層數(shù))select * from temp_cte關(guān)于 SQL Server 中怎么利用公用表表達(dá)式實(shí)現(xiàn)遞歸就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
