在TSQL腳本中，也能實(shí)現(xiàn)遞歸查詢，SQL Server提供CTE（Common Table Expression），只需要編寫少量的代碼，就能實(shí)現(xiàn)遞歸查詢，本文詳細(xì)介紹CTE遞歸調(diào)用的特性和使用示例，遞歸查詢主要用于層次結(jié)構(gòu)的查詢，從葉級(jí)（Leaf Level）向頂層（Root Level）查詢，或從頂層向葉級(jí)查詢，或遞歸的路徑（Path）。
一，遞歸查詢?cè)?/strong>
CTE的遞歸查詢必須滿足三個(gè)條件：初始條件，遞歸調(diào)用表達(dá)式，終止條件，CTE 遞歸查詢的偽代碼如下：
WITH cte_name ( column_name [,...n] )AS(--Anchor member is definedCTE_query_definition UNION ALL--Recursive member is defined referencing cte_nameCTE_query_definition )-- Statement using the CTESELECT *FROM cte_name
1，遞歸查詢至少包含兩個(gè)子查詢：
第一個(gè)子查詢稱作定點(diǎn)（Anchor）子查詢：定點(diǎn)查詢只是一個(gè)返回有效表的查詢，用于設(shè)置遞歸的初始值；
第二個(gè)子查詢稱作遞歸子查詢：該子查詢調(diào)用CTE名稱，觸發(fā)遞歸查詢，實(shí)際上是遞歸子查詢調(diào)用遞歸子查詢；
兩個(gè)子查詢使用union all，求并集；
2，CTE的遞歸終止條件
遞歸查詢沒有顯式的遞歸終止條件，只有當(dāng)遞歸子查詢返回空結(jié)果集（沒有數(shù)據(jù)行返回）或是超出了遞歸次數(shù)的最大限制時(shí)，才停止遞歸。
默認(rèn)的遞歸查詢次數(shù)是100，可以使用查詢提示（hint）：MAXRECURSION 控制遞歸的最大次數(shù)：OPTION( MAXRECURSION 16)；如果允許無限制的遞歸次數(shù)，使用查詢提示：option(maxrecursion 0)；當(dāng)遞歸查詢達(dá)到指定或默認(rèn)的 MAXRECURSION 數(shù)量限制時(shí)，SQL Server將結(jié)束查詢并返回錯(cuò)誤，如下：
The statement terminated. The maximum recursion 10 has been exhausted before statement completion.
事務(wù)執(zhí)行失敗，該事務(wù)包含的所有操作都被回滾。在產(chǎn)品環(huán)境中，慎用maxrecursion 查詢提示，推薦通過 where 條件限制遞歸的次數(shù)。
3，遞歸步驟
step1：定點(diǎn)子查詢?cè)O(shè)置CTE的初始值，即CTE的初始值Set0；
遞歸調(diào)用的子查詢過程：遞歸子查詢調(diào)用遞歸子查詢；
step2：遞歸子查詢第一次調(diào)用CTE名稱，CTE名稱是指CTE的初始值Set0，第一次執(zhí)行遞歸子查詢之后，CTE名稱是指結(jié)果集Set1；
step3：遞歸子查詢第二次調(diào)用CTE名稱，CTE名稱是指Set1，第二次執(zhí)行遞歸子查詢之后，CTE名稱是指結(jié)果集Set2；
step4：在第N次執(zhí)行遞歸子查詢時(shí)，CTE名稱是指Set(N-1)，遞歸子查詢都引用前一個(gè)遞歸子查詢的結(jié)果集；
Step5：如果遞歸子查詢返回空數(shù)據(jù)行，或超出遞歸次數(shù)的最大限制，停止遞歸；
二，遞歸查詢示例（員工職稱）
1，創(chuàng)建測(cè)試數(shù)據(jù)
ManagerID是UserID的父節(jié)點(diǎn)，這是一個(gè)非常簡(jiǎn)單的層次結(jié)構(gòu)模型。
View Code
2，查詢每個(gè)User的的直接上級(jí)Manager
;with cte as(select UserID,ManagerID,name,name as ManagerNamefrom dbo.dt_userwhere ManagerID=-1union allselect c.UserID,c.ManagerID,c.Name,p.name as ManagerNamefrom cte Pinner join dbo.dt_user c on p.UserID=c.ManagerID )select UserID,ManagerID,Name,ManagerNamefrom cteorder by UserID
step1：查詢ManagerID=-1，作為root node，這是遞歸查詢的起始點(diǎn)。
step2：迭代公式是 union all 下面的查詢語句。在查詢語句中調(diào)用中cte，而查詢語句就是cte的組成部分，即 “自己調(diào)用自己”，這就是遞歸的真諦所在。
所謂迭代，是指每一次遞歸都要調(diào)用上一次查詢的結(jié)果集，Union ALL是指每次都把結(jié)果集并在一起。
step3-N，迭代公式利用上一次查詢返回的結(jié)果集執(zhí)行特定的查詢，直到CTE返回null 或達(dá)到最大的迭代次數(shù)，默認(rèn)值是32。最終的結(jié)果集是迭代公式返回的各個(gè)結(jié)果集的并集，求并集是由Union All 子句定義的，并且只能使用Union ALL。
3，查詢路徑，在層次結(jié)構(gòu)中查詢子節(jié)點(diǎn)到父節(jié)點(diǎn)的path
;with cte as(select UserID,ManagerID,name,cast(name as nvarchar(max)) as ReportPathfrom dbo.dt_userwhere ManagerID=-1union allselect c.UserID,c.ManagerID,c.Name,c.name+'->'+p.ReportPath as ReportPathfrom cte Pinner join dbo.dt_user c on p.UserID=c.ManagerID )select UserID,ManagerID,Name,ReportPathfrom cteorder by UserID
查詢結(jié)果如下截圖：
三，遞歸查詢示例（行政區(qū)劃）
1，需求模擬
在TSQL中實(shí)現(xiàn)層次結(jié)構(gòu)，例如有這樣一種數(shù)據(jù)結(jié)構(gòu)，省，市，縣，鄉(xiāng)，村，如何使用一張表表示這種數(shù)據(jù)結(jié)構(gòu)，并且允許是不對(duì)稱的，例如，上海市是個(gè)直轄市，沒有省份。
create table dbo.hierarchy ( ID int not null primary key,--type int not null,ParentID int not null, name varchar(100) not null)
type表示類型，可以設(shè)置：省，Type是1；市，type是2，以此類推。
ParentID標(biāo)識(shí)的是父級(jí)ID，例如信陽市的ParentID是河南省的ID。
2，插入測(cè)試數(shù)據(jù)
測(cè)試數(shù)據(jù)格式說明了歸屬關(guān)系，博主懶，去掉type字段。
View Code
3，實(shí)現(xiàn)由父級(jí)向子級(jí)的查詢
由于實(shí)際的數(shù)據(jù)可能有很多，所以，要想獲取河南省下的所有市，縣，鄉(xiāng)，村等信息，必須使用遞歸查詢
;with cte(Id,ParentID,Name) as(select * from dbo.hierarchy where id=1union allselect h.* from dbo.hierarchy hinner join cte c on h.ParentID=c.id --where c.id!=h.ID)select *from cteorder by ParentID
如果要查看向內(nèi)遞歸到多少level，可以使用派生列，level=0是省level，level=1是市l(wèi)evel，依次類推。
;with cte(Id,ParentID,Name,Level) as(select ID,ParentID,Name,0 as Levelfrom dbo.hierarchy where id=1union allselect h.ID,h.ParentID,h.Name,c.Level+1 as Levelfrom dbo.hierarchy hinner join cte c on h.ParentID=c.id --where c.id!=h.ID)select *from cteorder by ParentID
查詢結(jié)果如圖：
4，由子級(jí)向父級(jí)的遞歸查詢
;with cte as(select ID,ParentID,namefrom dbo.hierarchywhere id=4 --蘆集鄉(xiāng)的IDunion allselect h.ID,h.ParentID,h.namefrom dbo.hierarchy hinner join cte c on h.id=c.ParentID )select ID,ParentID,namefrom cteorder by ParentID
查詢結(jié)果如圖：
延伸閱讀
- 我的JAVA生涯 ——解*泉 2020-01-15
- 萬碼學(xué)堂學(xué)員王*全 [青島一閣供應(yīng)鏈有限公司] 2019-12-11
- 多積累才能有進(jìn)步——?jiǎng)?聰 2019-10-28
- 很幸運(yùn)選擇了萬碼——穆*杰 2019-10-28
- 方法總比困難多——蘇*誠(chéng) 2019-10-28
- 從項(xiàng)目實(shí)訓(xùn)中學(xué)到的——王*全 2019-10-28
- 青春是用來奮斗的——顏*育 2019-10-28
- 等你回家 2019-10-27
- 新的挑戰(zhàn)——王*全 2019-07-05
- 項(xiàng)目的洗禮——王*全 2019-03-12
我的JAVA生涯 ——解*泉