SQL中的遞歸原理

162次閱讀

共計 2042 個字符，預計需要花費 6 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章主要介紹“SQL 中的遞歸原理”，在日常操作中，相信很多人在 SQL 中的遞歸原理問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”SQL 中的遞歸原理”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

遞歸查詢原理

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 的長度設置為最大，防止字段過長超出字段長度。具體結果如下：