在TSQL腳本中，也能實(shí)現(xiàn)遞歸查詢，SQL Server提供CTE（Common?Table?Expression），只需要編寫(xiě)少量的代碼，就能實(shí)現(xiàn)遞歸查詢，本文詳細(xì)介紹CTE遞歸調(diào)用的特性和使用示例，遞歸查詢主要用于層次結(jié)構(gòu)的查詢，從葉級(jí)（Leaf Level）向頂層（Root Level）查詢，或從頂層向葉級(jí)查詢，或遞歸的路徑（Path）。

一，遞歸查詢?cè)?/p>

CTE的遞歸查詢必須滿足三個(gè)條件：初始條件，遞歸調(diào)用表達(dá)式，終止條件，CTE 遞歸查詢的偽代碼如下：

WITH?cte_name?(?column_name?[,...n]?)
AS
(
--Anchor?member?is?defined
CTE_query_definition?
UNION?ALL
--Recursive?member?is?defined?referencing?cte_name
CTE_query_definition?
)
--?Statement?using?the?CTE
SELECT?*
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的遞歸終止條件

遞歸查詢沒(méi)有顯式的遞歸終止條件，只有當(dāng)遞歸子查詢返回空結(jié)果集（沒(méi)有數(shù)據(jù)行返回）或是超出了遞歸次數(shù)的最大限制時(shí)，才停止遞歸。

默認(rèn)的遞歸查詢次數(shù)是100，可以使用查詢提示（hint）：MAXRECURSION 控制遞歸的最大次數(shù)：OPTION( MAXRECURSION 16)；如果允許無(wú)限制的遞歸次數(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 查詢提示，推薦通過(guò) where 條件限制遞歸的次數(shù)。

3，遞歸步驟

step1：定點(diǎn)子查詢?cè)O(shè)置CTE的初始值，即CTE的初始值Set0；

遞歸調(diào)用的子查詢過(guò)程：遞歸子查詢調(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)模型。

use?tempdb
go?

create?table?dbo.dt_user
(
????UserID?int,
????ManagerID?int,
????Name?Nvarchar(10)
)

insert?into?dbo.dt_user
select?1,-1,N'Boss'
union?all
select?11,1,N'A1'
union?all
select?12,1,N'A2'
union?all
select?13,1,N'A3'
union?all
select?111,11,N'B1'
union?all
select?112,11,N'B2'
union?all
select?121,12,N'C1'

2，查詢每個(gè)User的的直接上級(jí)Manager

;with?cte?as
(
select?UserID,ManagerID,name,name?as?ManagerName
from?dbo.dt_user
where?ManagerID=-1

union?all
select?c.UserID,c.ManagerID,c.Name,p.name?as?ManagerName
from?cte?P
inner?join?dbo.dt_user?c
????on?p.UserID=c.ManagerID
)
select?UserID,ManagerID,Name,ManagerName
from?cte
order?by?UserID

step1：查詢ManagerID=-1，作為root node，這是遞歸查詢的起始點(diǎn)。

step2：迭代公式是 union all 下面的查詢語(yǔ)句。在查詢語(yǔ)句中調(diào)用中cte，而查詢語(yǔ)句就是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?ReportPath
from?dbo.dt_user
where?ManagerID=-1

union?all
select?c.UserID,c.ManagerID,c.Name,c.name+'->'+p.ReportPath?as?ReportPath
from?cte?P
inner?join?dbo.dt_user?c
????on?p.UserID=c.ManagerID
)
select?UserID,ManagerID,Name,ReportPath
from?cte
order?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è)直轄市，沒(méi)有省份。

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，例如信陽(yáng)市的ParentID是河南省的ID。

2，插入測(cè)試數(shù)據(jù)

測(cè)試數(shù)據(jù)格式說(shuō)明了歸屬關(guān)系，博主懶，去掉type字段。

insert?into?dbo.hierarchy
values(1,0,'河南省')
,(2,1,'信陽(yáng)市'),(3,2,'淮濱縣'),(4,3,'蘆集鄉(xiāng)'),(12,3,'鄧灣鄉(xiāng)'),(13,3,'臺(tái)頭鄉(xiāng)'),(14,3,'谷堆鄉(xiāng)')
??????????????,(8,2,'固始縣'),(9,8,'李店鄉(xiāng)')
??????????????,(10,2,'息縣'),(11,10,'關(guān)店鄉(xiāng)')
,(5,1,'安陽(yáng)市'),(6,5,'滑縣'),(7,6,'老廟鄉(xiāng)')
,(15,1,'南陽(yáng)市'),(16,15,'方城縣')
,(17,1,'駐馬店市'),(18,17,'正陽(yáng)縣')


select?*?
from?dbo.hierarchy?
order?by?ParentID

3，實(shí)現(xiàn)由父級(jí)向子級(jí)的查詢

由于實(shí)際的數(shù)據(jù)可能有很多，所以，要想獲取河南省下的所有市，縣，鄉(xiāng)，村等信息，必須使用遞歸查詢

;with?cte(Id,ParentID,Name)?as
(
select?*?
from?dbo.hierarchy?
where?id=1

union?all
select?h.*?
from?dbo.hierarchy?h
inner?join?cte?c?on?h.ParentID=c.id?
--where?c.id!=h.ID
)
select?*
from?cte
order?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?Level
from?dbo.hierarchy?
where?id=1

union?all
select?h.ID,h.ParentID,h.Name,c.Level+1?as?Level
from?dbo.hierarchy?h
inner?join?cte?c?on?h.ParentID=c.id?
--where?c.id!=h.ID
)
select?*
from?cte
order?by?ParentID

查詢結(jié)果如圖：

4，由子級(jí)向父級(jí)的遞歸查詢

;with?cte?as
(
select?ID,ParentID,name
from?dbo.hierarchy
where?id=4?--蘆集鄉(xiāng)的ID

union?all
select?h.ID,h.ParentID,h.name
from?dbo.hierarchy?h
inner?join?cte?c?on?h.id=c.ParentID
)
select?ID,ParentID,name
from?cte
order?by?ParentID

查詢結(jié)果如圖：

?

參考文檔：

Recursive Queries Using Common Table Expressions

WITH common_table_expression (Transact-SQL)