遞歸查詢原理

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 
)

遞歸查詢示例

創建測試數據，有一個員工表Company，父級ID是部門ID的父節點，這是一個非常簡單的層次結構模型。

USE SQL_Road
GO 
CREATE  TABLE Company
(
    部門ID INT,
    父級ID  INT,
    部門名稱 VARCHAR(10)
)
 INSERT  INTO Company VALUES 
(1,-1,'總部'),
(11,1,'財務中心'),
(12,1,'人力中心'),
(13,1,'信息中心'),
(111,11,'會計組'),
(112,11,'出納組'),
(121,12,'薪酬組')

查詢一下Company表里的數據：

查詢每個部門的的直接上級ID：

WITH CTE AS(
 SELECT 部門ID,父級ID,部門名稱,部門名稱 AS 父級部門名稱
 FROM Company
 WHERE 父級ID=-1
 UNION ALL
 SELECT c.部門ID,c.父級ID,c.部門名稱,p.部門名稱 AS 父級部門名稱
 FROM CTE P
 INNER JOIN Company c ON p.部門ID=c.父級ID
)

SELECT 部門ID,父級ID,部門名稱,父級部門名稱
FROM CTE

結果如下：

我們來解讀一下上面的代碼：

1.查詢父級ID=-1，作為根節點，這是遞歸查詢的起始點。

2.迭代公式是 UNION ALL 下面的查詢語句。在查詢語句中調用中CTE，而查詢語句就是CTE的組成部分，即 “自己調用自己”，這就是遞歸的真諦所在。

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

3.迭代公式利用上一次查詢返回的結果集執行特定的查詢，直到CTE返回NULL或達到最大的迭代次數，默認值是32。最終的結果集是迭代公式返回的各個結果集的并集，求并集是由UNION ALL 子句定義的，并且只能使用UNION ALL

查詢路徑

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

WITH CTE AS(
 SELECT 部門ID,父級ID,部門名稱,CAST(部門名稱 AS NVARCHAR(MAX)) AS 部門路徑
 FROM Company
 WHERE 父級ID=-1
 UNION ALL
 SELECT c.部門ID,c.父級ID,c.部門名稱,p.部門路徑+'->'+c.部門名稱 AS 部門路徑
 FROM CTE P
 INNER JOIN Company c ON p.部門ID=c.父級ID
)

SELECT 部門ID,父級ID,部門名稱,部門路徑
FROM CTE

其中CAST(部門名稱 AS VARCHAR(MAX))是將部門名稱的長度設置為最大，防止字段過長超出字段長度。具體結果如下：

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