您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 樹形表非循環遞歸查詢：CTE 遞歸查詢完整指南

編輯：關於SqlServer

前言

在處理樹形結構數據（如組織架構、地區劃分、分類目錄）時，傳統做法往往採用循環或游標進行遞歸查詢，不僅代碼冗長，效率也較低。自 SQL Server 2005 起，微軟引入了公用表表達式（CTE），配合 UNION ALL 即可優雅地實現遞歸查詢，無需顯式循環。

核心語法：遞歸 CTE

遞歸 CTE 由兩部分組成：錨點成員（Anchor Member）與遞歸成員（Recursive Member），透過 UNION ALL 連接。

場景一：由子節點向上查詢所有父節點（祖先鏈）

WITH TreeCTE AS (
    -- 錨點：起始節點
    SELECT Id, PId, Area, 1 AS Level
    FROM Areas
    WHERE Id = 6  -- 指定子節點 ID
    
    UNION ALL
    
    -- 遞歸：向上尋找父節點
    SELECT a.Id, a.PId, a.Area, t.Level + 1
    FROM Areas a
    INNER JOIN TreeCTE t ON t.PId = a.Id
)
SELECT Area FROM TreeCTE ORDER BY Level DESC;

場景二：由父節點向下查詢所有子節點（後代樹）

WITH TreeCTE AS (
    SELECT Id, PId, Area, 1 AS Level
    FROM Areas
    WHERE Id = 7  -- 指定父節點 ID
    
    UNION ALL
    
    SELECT a.Id, a.PId, a.Area, t.Level + 1
    FROM Areas a
    INNER JOIN TreeCTE t ON t.Id = a.PId
)
SELECT Area FROM TreeCTE ORDER BY Level;

進階應用：彙總為路徑字串

若需將查詢結果合併為「中國 > 北京市 > 豐台區」格式，可搭配變量彙總：

DECLARE @Path VARCHAR(8000) = '';
WITH TreeCTE AS (
    SELECT Id, PId, Area
    FROM Areas WHERE Id = 6
    UNION ALL
    SELECT a.Id, a.PId, a.Area
    FROM Areas a INNER JOIN TreeCTE t ON t.PId = a.Id
)
SELECT @Path = @Path + Area + ' > ' FROM TreeCTE ORDER BY Id;
SELECT Path = LEFT(@Path, LEN(@Path) - 3);  -- 移除尾部分隔符

封裝為預存程序

CREATE PROCEDURE dbo.usp_GetAreaPath
    @StartId INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Result VARCHAR(8000) = '';
    
    WITH TreeCTE AS (
        SELECT Id, PId, Area FROM Areas WHERE Id = @StartId
        UNION ALL
        SELECT a.Id, a.PId, a.Area
        FROM Areas a INNER JOIN TreeCTE t ON t.PId = a.Id
    )
    SELECT @Result = @Result + Area + ' > ' FROM TreeCTE ORDER BY Id;
    
    SELECT AreaPath = CASE WHEN LEN(@Result) > 0 
                           THEN LEFT(@Result, LEN(@Result) - 3) 
                           ELSE '' END;
END;