共用方式為

使用通用資料表表示式的遞迴查詢 （Transact-SQL）

適用於：SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics分析平台系統（PDW）Microsoft Fabric 中的 SQL 資料庫

通用數據表表示式 （CTE） 提供能夠參考本身的顯著優勢，因此會建立遞歸 CTE。 遞歸 CTE 是重複執行初始 CTE 以傳回數據子集的 CTE，直到取得完整的結果集為止。

當查詢參考遞歸 CTE 時，查詢稱為遞歸查詢。 傳回階層式數據是遞歸查詢的常見用法。 例如，在組織結構中顯示員工，或在父產品有一或多個元件且這些元件可能有子元件或可能是其他父系的元件之材料帳單案例中的數據。

遞歸 CTE 可以大幅簡化在 、SELECT、、 INSERTUPDATE或 DELETE 語句內CREATE VIEW執行遞歸查詢所需的程式代碼。 在舊版 SQL Server 中，遞歸查詢通常需要使用臨時表、數據指標和邏輯來控制遞歸步驟的流程。 如需通用數據表表達式的詳細資訊，請參閱 WITH common_table_expression。

在 Microsoft Fabric 中，Fabric Data Warehouse 和 SQL 分析端點都支援標準、循序和 巢狀 CTE，但不支援遞迴 CTE。

遞歸 CTE 的結構

Transact-SQL 中遞歸 CTE 的結構類似於其他程式設計語言中的遞歸例程。 雖然其他語言中的遞歸例程會傳回純量值，但遞歸 CTE 可以傳回多個數據列。

遞迴 CTE 包含三個元素：

  1. 例程的調用。

    遞歸 CTE 的第一個調用是由 、、 UNION ALLUNION或 EXCEPT 運算符聯INTERSECT結的一或多個 CTE 查詢定義所組成。 由於這些查詢定義構成 CTE 結構的基底結果集，因此稱為錨點成員。

    除非 CTE 本身參考 CTE 本身，否則 CTE 查詢定義會被視為錨點成員。 所有錨點成員查詢定義都必須放在第一個遞歸成員定義之前，而且運算符必須用來聯結最後一個 UNION ALL 錨點成員與第一個遞歸成員。

  2. 例程的遞歸調用。

    遞歸調用包含一或多個 CTE 查詢定義，由 UNION ALL 參考 CTE 本身的運算符聯結。 這些查詢定義稱為遞歸成員。

  3. 終止檢查。

    終止檢查是隱含的;當先前的調用未傳回任何數據列時，遞歸會停止。

Note

撰寫不正確的遞歸 CTE 可能會導致無限迴圈。 例如，若遞迴成員查詢定義對父資料行與子資料行傳回相同的值，就會建立無限迴圈。 測試遞歸查詢的結果時，您可以在 、 MAXRECURSIONOPTION、 或 INSERT 語句的 子句UPDATE中使用DELETESELECT提示和介於 0 到 32,767 之間的值，來限制特定語句允許的遞歸層級數目。

如需詳細資訊，請參閱：

虛擬程式代碼和語意

遞歸 CTE 結構必須至少包含一個錨點成員和一個遞歸成員。 下列虛擬程式代碼顯示包含單一錨點成員和單一遞歸成員之簡單遞歸 CTE 的元件。

WITH cte_name ( column_name [ ,...n ] )
AS
(
    CTE_query_definition -- Anchor member is defined.
    UNION ALL
    CTE_query_definition -- Recursive member is defined referencing cte_name.
)

-- Statement using the CTE
SELECT *
FROM cte_name

遞迴執行的語意如下：

  1. 將 CTE 運算式分割成錨點和遞歸成員。
  2. 執行錨點成員，建立第一個叫用或基底結果集 （T0）。
  3. 以 輸入Ti和 + 1 作為輸出執行遞歸成員Ti。
  4. 重複步驟 3，直到傳回空集為止。
  5. 傳回結果集。 這是 UNION ALLT0 的 Tn。

Examples

下列範例顯示遞歸 CTE 結構的語意，方法是傳回階層式員工清單，從資料庫中的最高排名員工 AdventureWorks2025 開始。 程式代碼執行的逐步解說遵循範例。

建立員工資料表：

CREATE TABLE dbo.MyEmployees
(
    EmployeeID SMALLINT NOT NULL,
    FirstName NVARCHAR (30) NOT NULL,
    LastName NVARCHAR (40) NOT NULL,
    Title NVARCHAR (50) NOT NULL,
    DeptID SMALLINT NOT NULL,
    ManagerID INT NULL,
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);

使用值填入資料表：

INSERT INTO dbo.MyEmployees
VALUES
    (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
    (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
    (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
    (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
    (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
    (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
    (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
    (16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
    (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
GO

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS (
-- Anchor member definition
SELECT e.ManagerID,
           e.EmployeeID,
           e.Title,
           edh.DepartmentID,
           0 AS Level
    FROM dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID,
           e.EmployeeID,
           e.Title,
           edh.DepartmentID,
           Level + 1
    FROM dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID)
-- Statement that executes the CTE
SELECT ManagerID,
       EmployeeID,
       Title,
       DeptID,
       Level
FROM DirectReports
     INNER JOIN HumanResources.Department AS dp
         ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing'
      OR Level = 0;
GO

範例程式代碼逐步解說

遞歸 CTE 會 DirectReports定義一個錨點成員和一個遞歸成員。

錨點成員會傳回基底結果集 T0。 這是公司中排名最高的員工。 也就是說，未向經理回報的員工。

以下是錨點成員所傳回的結果集：

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer        0

遞歸成員會傳回錨點成員結果集中員工的直接下屬。 這可透過 Employee 資料表與 DirectReports CTE 之間的聯結作業來達成。 這是 CTE 本身的參考，可建立遞歸調用。 根據 CTE DirectReports 中的員工做為輸入 （Ti），聯結 （） 會以輸出 （MyEmployees.ManagerID = DirectReports.EmployeeID+ 1） 的形式傳回，而擁有 （TiTi） 作為其經理的員工。

因此，遞歸成員的第一個反覆項目會傳回此結果集：

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1

遞歸成員會重複啟動。 遞歸成員的第二個反覆專案會使用步驟 3 中單一數據列結果集（包含 EmployeeID 的 273）作為輸入值，並傳回此結果集：

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2

遞歸成員的第三個反覆專案會使用先前的結果集做為輸入值，並傳回此結果集：

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

執行中查詢所傳回的最終結果集是錨點和遞歸成員所產生的所有結果集聯集。

結果如下。

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

相關內容

  • Last updated on