一篇學會SQL中的遞歸的用法
本文轉載自微信公眾號「SQL數據庫開發」，作者丶平凡世界。轉載本文請聯系SQL數據庫開發公眾號。
遞歸查詢原理
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
結果如下：
我們來解讀一下上面的代碼
1、查詢ManagerID=-1，作為根節點，這是遞歸查詢的起始點。
2、迭代公式是 UNION ALL 下面的查詢語句。在查詢語句中調用中CTE，而查詢語句就是CTE的組成部分，即 “自己調用自己”，這就是遞歸的真諦所在。
所謂迭代，是指每一次遞歸都要調用上一次查詢的結果集，UNION ALL是指每次都把結果集并在一起。
3、迭代公式利用上一次查詢返回的結果集執行特定的查詢，直到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的長度設置為最大，防止字段過長超出字段長度。具體結果如下：
以上就是遞歸查詢的一些知識介紹了，自己可以動手實驗一下，這個一般在面試中也經常會考察面試者，希望能幫助到大家~
