SQL中的遞迴用法

導讀	遞迴查詢沒有顯式的遞迴終止條件，只有當第二個遞迴查詢返回空結果集或是超出了遞迴次數的最大限制時才停止遞迴。是指遞迴次數上限的方法是使用MAXRECURION。

SQL Server中的遞迴查詢是透過CTE(表表示式)來實現。至少包含兩個查詢，第一個查詢為定點成員，定點成員只是一個返回有效表的查詢，用於遞迴的基礎或定位點;第二個查詢被稱為遞迴成員，使該查詢稱為遞迴成員的是對CTE名稱的遞迴引用是觸發。在邏輯上可以將CTE名稱的內部應用理解為前一個查詢的結果集。

遞迴查詢沒有顯式的遞迴終止條件，只有當第二個遞迴查詢返回空結果集或是超出了遞迴次數的最大限制時才停止遞迴。是指遞迴次數上限的方法是使用MAXRECURION。

效率高，大量資料集下，速度比程式的查詢快。

WITH CTE AS (

SELECT column1,column2... FROM tablename WHERE conditions

UNION ALL

SELECT column1,column2... FROM tablename

INNER JOIN CTE ON conditions

)

建立測試資料，有一個員工表Employee，ManagerID是UserID的父節點，這是一個非常簡單的層次結構模型。

USE SQL_Road 
GO  
CREATE  TABLE Employee 
( 
    UserID INT, 
    ManagerID INT, 
    Name NVARCHAR(10) 
) 
 INSERT  INTO dbo.Employee 
 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'

查詢一下Employee表裡的資料

查詢每個User的的直接上級Manager

WITH CTE AS( 
 SELECT UserID,ManagerID,Name,Name AS ManagerName 
 FROM dbo.Employee 
 WHERE ManagerID=-1 
 UNION ALL 
 SELECT c.UserID,c.ManagerID,c.Name,p.Name AS ManagerName 
 FROM CTE P 
 INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID 
) 
 
SELECT UserID,ManagerID,Name,ManagerName 
FROM CTE

結果如下：

我們來解讀一下上面的程式碼

• 查詢ManagerID=-1，作為根節點，這是遞迴查詢的起始點。
• 迭代公式是 UNION ALL 下面的查詢語句。在查詢語句中呼叫中CTE，而查詢語句就是CTE的組成部分，即 “自己呼叫自己”，這就是遞迴的真諦所在。所謂迭代，是指每一次遞迴都要呼叫上一次查詢的結果集，UNION ALL是指每次都把結果集並在一起。
• 迭代公式利用上一次查詢返回的結果集執行特定的查詢，直到CTE返回NULL或達到最大的迭代次數，預設值是32。最終的結果集是迭代公式返回的各個結果集的並集，求並集是由UNION ALL 子句定義的，並且只能使用UNION ALL
查詢路徑

下面我們透過層次結構查詢子節點到父節點的PATH，我們對上面的程式碼稍作修改：

WITH CTE AS( 
 SELECT UserID,ManagerID,Name,CAST(Name AS NVARCHAR(MAX)) AS LPath  
 FROM dbo.Employee 
 WHERE ManagerID=-1 
 UNION ALL 
 SELECT c.UserID,c.ManagerID,c.Name,p.LPath+'->'+c.Name AS LPath 
 FROM CTE P 
 INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID 
 ) 
 
SELECT UserID,ManagerID,Name,LPath 
FROM CTE

其中CAST(Name AS NVARCHAR(MAX))是將Name的長度設定為最大，防止欄位過長超出欄位長度。具體結果如下：

以上就是遞迴查詢的一些知識介紹了，自己可以動手實驗一下，這個一般在面試中也經常會考察面試者，希望能幫助到大家~

原文來自：