SQL Server CTE 遞歸查詢全解

時間：2018-12-28 16:12:01

關鍵字： server sql 遞歸

手機看文章

掃描二維碼
隨時隨地手機看文章

[導讀]在TSQL腳本中，也能實現遞歸查詢，SQL Server提供CTE（Common?Table?Expression），只需要編寫少量的代碼，就能實現遞歸查詢，本文詳細介紹CTE遞歸調用的特性和使用示例

在TSQL腳本中，也能實現遞歸查詢，SQL Server提供CTE（Common?Table?Expression），只需要編寫少量的代碼，就能實現遞歸查詢，本文詳細介紹CTE遞歸調用的特性和使用示例，遞歸查詢主要用于層次結構的查詢，從葉級（Leaf Level）向頂層（Root Level）查詢，或從頂層向葉級查詢，或遞歸的路徑（Path）。

一，遞歸查詢原理

CTE的遞歸查詢必須滿足三個條件：初始條件，遞歸調用表達式，終止條件，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，遞歸查詢至少包含兩個子查詢：

第一個子查詢稱作定點（Anchor）子查詢：定點查詢只是一個返回有效表的查詢，用于設置遞歸的初始值；第二個子查詢稱作遞歸子查詢：該子查詢調用CTE名稱，觸發(fā)遞歸查詢，實際上是遞歸子查詢調用遞歸子查詢；兩個子查詢使用union all，求并集；

2，CTE的遞歸終止條件

遞歸查詢沒有顯式的遞歸終止條件，只有當遞歸子查詢返回空結果集（沒有數據行返回）或是超出了遞歸次數的最大限制時，才停止遞歸。

默認的遞歸查詢次數是100，可以使用查詢提示（hint）：MAXRECURSION 控制遞歸的最大次數：OPTION( MAXRECURSION 16)；如果允許無限制的遞歸次數，使用查詢提示：option(maxrecursion 0)；當遞歸查詢達到指定或默認的 MAXRECURSION 數量限制時，SQL Server將結束查詢并返回錯誤，如下：

The statement terminated. The maximum recursion 10 has been exhausted before statement completion.

事務執(zhí)行失敗，該事務包含的所有操作都被回滾。在產品環(huán)境中，慎用maxrecursion 查詢提示，推薦通過 where 條件限制遞歸的次數。

3，遞歸步驟

step1：定點子查詢設置CTE的初始值，即CTE的初始值Set0；

遞歸調用的子查詢過程：遞歸子查詢調用遞歸子查詢；

step2：遞歸子查詢第一次調用CTE名稱，CTE名稱是指CTE的初始值Set0，第一次執(zhí)行遞歸子查詢之后，CTE名稱是指結果集Set1；

step3：遞歸子查詢第二次調用CTE名稱，CTE名稱是指Set1，第二次執(zhí)行遞歸子查詢之后，CTE名稱是指結果集Set2；

step4：在第N次執(zhí)行遞歸子查詢時，CTE名稱是指Set(N-1)，遞歸子查詢都引用前一個遞歸子查詢的結果集；

Step5：如果遞歸子查詢返回空數據行，或超出遞歸次數的最大限制，停止遞歸；

二，遞歸查詢示例（員工職稱）

1，創(chuàng)建測試數據

ManagerID是UserID的父節(jié)點，這是一個非常簡單的層次結構模型。

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，查詢每個User的的直接上級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，這是遞歸查詢的起始點。

step2：迭代公式是 union all 下面的查詢語句。在查詢語句中調用中cte，而查詢語句就是cte的組成部分，即 “自己調用自己”，這就是遞歸的真諦所在。

所謂迭代，是指每一次遞歸都要調用上一次查詢的結果集，Union ALL是指每次都把結果集并在一起。

step3-N，迭代公式利用上一次查詢返回的結果集執(zhí)行特定的查詢，直到CTE返回null 或達到最大的迭代次數，默認值是32。最終的結果集是迭代公式返回的各個結果集的并集，求并集是由Union All 子句定義的，并且只能使用Union ALL。

3，查詢路徑，在層次結構中查詢子節(jié)點到父節(jié)點的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

查詢結果如下截圖：

三，遞歸查詢示例（行政區(qū)劃）

1，需求模擬

在TSQL中實現層次結構，例如有這樣一種數據結構，省，市，縣，鄉(xiāng)，村，如何使用一張表表示這種數據結構，并且允許是不對稱的，例如，上海市是個直轄市，沒有省份。

create?table?dbo.hierarchy
(
ID??int?not?null?primary?key,
--type?int?not?null,
ParentID?int?not?null,
name?varchar(100)?not?null
)

type表示類型，可以設置：省，Type是1；市，type是2，以此類推。

ParentID標識的是父級ID，例如信陽市的ParentID是河南省的ID。

2，插入測試數據

測試數據格式說明了歸屬關系，博主懶，去掉type字段。

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


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

3，實現由父級向子級的查詢

由于實際的數據可能有很多，所以，要想獲取河南省下的所有市，縣，鄉(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

如果要查看向內遞歸到多少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

查詢結果如圖：

4，由子級向父級的遞歸查詢

;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

查詢結果如圖：

參考文檔：

Recursive Queries Using Common Table Expressions

WITH common_table_expression (Transact-SQL)