SQL Server---实现递归查询（使用公共表表达式）

1.语法

WITH cte_name ( column_name [,...n] )

AS

(

    --定点成员（锚成员） 

   SELECT column_name [,...n] FROM tablename WHERE conditions

   UNION ALL

   --递归成员

   SELECT column_name [,...n] FROM cte_name  INNER JOIN CTE ON conditions 

)

-- Statement using the CTE

SELECT *  FROM cte_name  

2.递归查询原理

SQL Server中的递归查询是通过CTE(表表达式)来实现。至少包含两个查询，第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点；第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

3.递归CTE结构

Transact-SQL 中递归 CTE 的结构类似于其他编程语言中的递归例程。尽管其他语言中的递归例程返回标量值，但递归 CTE 可以返回多行。

递归 CTE 由三个元素组成：

1. 调用例程。

   递归 CTE 的第一次调用由一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符连接的 CTE_query_definitions 组成。因为这些查询定义构成了 CTE 结构的基本结果集，所以它们被称为定点成员（或锚成员）。

   as后的第一个查询语句 被视为定点成员（或锚成员），除非它们引用 CTE 本身。所有定点成员查询定义必须位于第一个递归成员定义之前，并且必须使用 UNION ALL 运算符将最后一个锚成员与第一个递归成员连接起来。

2. 递归调用例程。

   递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符连接的 查询语句。这些查询定义称为递归成员。

3. 终止检查。

   终止检查是隐式的；当前一次调用没有返回任何行时，递归停止。

4.案例

1.创建表及添加数据

 -- Create an Employee table.
    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) 
    );
    -- Populate the table with values.
    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);

2.查询所有员工信息以及其所处级别

 WITH DirectReports (EmployeeID,ManagerID, Title, DeptID, Level)
    AS
    (
    -- Anchor member definition
        SELECT EmployeeID, 0 as ManagerID, Title, DeptID, 
            0 AS Level
        FROM dbo.MyEmployees where ManagerID is null
        UNION ALL
    -- Recursive member definition
        SELECT  e.EmployeeID,e.ManagerID, e.Title, e.DeptID,
            Level + 1
        FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
            ON e.ManagerID = d.EmployeeID
    )
    -- Statement that executes the CTE
    SELECT EmployeeID,ManagerID, Title, DeptID, Level
    FROM DirectReports 

查询结果：

 

 3.查询EmployeeID=273的员工及其所有下属信息（含下属的下属,递归下属）

 WITH CTE 
    AS
    (
        SELECT EmployeeID, ManagerID, Title, DeptID
        FROM dbo.MyEmployees where EmployeeID=273
        UNION ALL
        SELECT  e.EmployeeID,e.ManagerID, e.Title, e.DeptID
        FROM dbo.MyEmployees AS e
        INNER JOIN CTE AS c
            ON e.ManagerID = c.EmployeeID
    )
    SELECT EmployeeID,ManagerID, Title, DeptID
    FROM CTE 

查询结果：

 

 3.查询 员工编号为275及其所有上级的信息（递归上级）

 WITH CTE 
    AS
    (
        SELECT EmployeeID, ManagerID, Title, DeptID
        FROM dbo.MyEmployees where EmployeeID=275
        UNION ALL
        SELECT  e.EmployeeID,e.ManagerID, e.Title, e.DeptID
        FROM dbo.MyEmployees AS e
        INNER JOIN CTE AS c
            ON e.EmployeeID = c.ManagerID
    )
    SELECT EmployeeID,ManagerID, Title, DeptID
    FROM CTE 

查询结果：

 

 原文参考：

WITH common_table_expression (Transact-SQL) | Microsoft Docs

Recursive Queries Using Common Table Expressions | Microsoft Docs

https://blog.csdn.net/liyue071714118/article/details/119657683