SQL Server遞歸查詢語句詳解

一、遞歸查詢的概念

遞歸查詢是指一個查詢語句可以調用自身的執行方式，這種方式通常被用於處理關係型資料庫中的層次結構數據，比如組織機構、產品類別和客戶等數據。

在關係型資料庫中，層次結構數據在父子關係的基礎上定義了一個樹形結構，每個節點可以包含多個子節點，並有一個唯一的根節點。所以在處理這樣的數據時，遞歸查詢是一種非常強大的工具。

SQL Server中使用遞歸查詢可以通過WITH語句來實現，這個語句返回一個由多個子查詢組成的結果集，每個子查詢都是通過遞歸調用自身來實現的。

二、遞歸查詢的語法

遞歸查詢的語法如下：

WITH RecursiveCTE (Column1, Column2, Column3, …...)
AS
(
    -- Initial SELECT statement
    SELECT …...
    UNION ALL
    -- Recursive SELECT statement
    SELECT …...
    FROM RecursiveCTE
    WHERE ……
)
SELECT Column1, Column2, Column3, …...
FROM RecursiveCTE
WHERE ……;

遞歸查詢主要包含三部分：

1. 初始查詢語句，即遞歸過程的第一步，通常被稱為「Anchor Member」，它提供了一個起點。
2. 遞歸查詢語句，即執行遞歸操作的語句，它使用了遞歸調用，直到滿足某個條件停止。
3. 從遞歸查詢結果集中選擇所需的數據。

三、遞歸查詢的使用場景

遞歸查詢通常被用來處理關係資料庫中的層次結構數據，包括組織機構、產品類別、客戶等。

例如，在組織機構中，每個部門都可以包含多個子部門，但是每個子部門也可以有自己的子部門。在這種情況下，遞歸查詢可以快速地找到一個部門下的所有子部門。

四、遞歸查詢的實際應用

1. 組織結構查詢

組織結構通常通過層次結構數據來組織的，我們可以使用遞歸查詢來查詢某個組織節點下面的所有子節點。下面是一個示例：

--創建示例表
CREATE TABLE [dbo].[Organization](
    [ID] [int] PRIMARY KEY,
    [Name] [nvarchar](50) NOT NULL,
    [ParentID] [int] NULL
)

--插入示例數據
INSERT INTO [dbo].[Organization] VALUES (1, '總公司', NULL);
INSERT INTO [dbo].[Organization] VALUES (2, '分支機構1', 1);
INSERT INTO [dbo].[Organization] VALUES (3, '分支機構2', 1);
INSERT INTO [dbo].[Organization] VALUES (4, '分支機構1-1', 2);
INSERT INTO [dbo].[Organization] VALUES (5, '分支機構1-2', 2);
INSERT INTO [dbo].[Organization] VALUES (6, '分支機構2-1', 3);
INSERT INTO [dbo].[Organization] VALUES (7, '分支機構2-2', 3);
INSERT INTO [dbo].[Organization] VALUES (8, '分支機構1-1-1', 4);

--使用遞歸查詢查詢所有子節點
WITH RecursiveCTE AS (
    SELECT ID, Name, ParentID FROM [dbo].[Organization] WHERE ID = 1
    UNION ALL
    SELECT o.ID, o.Name, o.ParentID FROM RecursiveCTE AS r
    INNER JOIN [dbo].[Organization] AS o ON r.ID = o.ParentID
)
SELECT * FROM RecursiveCTE;

2. 級聯刪除

級聯刪除是指刪除一個父節點時，同時刪除其所有子節點的操作，我們可以使用遞歸查詢來實現。

下面是一個示例，刪除編號為1的記錄時，同時刪除其所有子節點的記錄：

--使用遞歸查詢刪除所有子節點
WITH RecursiveCTE AS (
    SELECT ID FROM [dbo].[Organization] WHERE ID = 1
    UNION ALL
    SELECT o.ID FROM RecursiveCTE AS r
    INNER JOIN [dbo].[Organization] AS o ON r.ID = o.ParentID
)
DELETE FROM [dbo].[Organization] WHERE ID IN (SELECT ID FROM RecursiveCTE);

3. 路徑查詢

路徑查詢是指查詢兩個節點之間的路徑的操作，我們可以使用遞歸查詢來實現。

下面是一個示例，查詢從根節點到編號為8的節點的路徑：

--使用遞歸查詢查詢路徑
WITH RecursiveCTE AS (
    SELECT ID, CAST(Name AS varchar(1000)) AS Path FROM [dbo].[Organization] WHERE ID = 1
    UNION ALL
    SELECT o.ID, r.Path + ' >> ' + o.Name FROM RecursiveCTE AS r
    INNER JOIN [dbo].[Organization] AS o ON r.ID = o.ParentID
)
SELECT Path FROM RecursiveCTE WHERE ID = 8;

五、遞歸查詢的注意事項

1. 遞歸查詢必須有一個停止條件，否則會發生死循環。
2. 遞歸查詢可能會產生很多子查詢，因此會佔用大量內存，需要慎重使用。
3. 遞歸查詢嵌套層數不能太深，否則執行效率會大大降低。