接纳CTE公用表表达式的递归查询(WITH AS)

公用表表明式 (CTE)
具备三个第一的长处,那便是可以引用其本身,进而开创递归 CTE。递归 CTE
是贰个重复施行起始 CTE 以回到数据子集直到获取完整结果集的公用表表明式。

当有个别查询援用递归 CTE
时,它即被称之为递归查询。递归查询普通用于重返分层数据,举个例子:显示某些组织图中的雇员或物品清单方案(在那之中父级产品有七个或四个零部件,而那几个组件恐怕还应该有子组件,或然是其余父级产品的机件)中的数据。

递归 CTE 能够十分大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW
语句中运作递归查询所需的代码。在 SQL Server
的前期版本中,递归查询普通必要选拔不时表、游标和逻辑来支配递归步骤流。

 

递归 CTE 的结构

Transact-SQL 中的递归 CTE
的布局与另外编制程序语言中的递归例程相似。固然别的语言中的递归例程重临标量值,但递归
CTE 可以回来多行。

递归 CTE 由下列几个元素构成:

  1. 例程的调用。

    递归 CTE 的率先个调用包罗一个或多个由 UNION ALL、UNION、EXCEPT 或
    INTE福特ExplorerSECT 运算符联接的
    CTE_query_definitions。由于这么些查询定义形成了 CTE
    结构的尺码结果集,所以它们被誉为“定位点成员”。

    CTE_query_definitions 被视为定位点成员,除非它们引用了 CTE
    本人。全体定位点成员查询定义必得放置在率先个递归成员定义从前,並且必得使用
    UNION ALL 运算符联接最终八个定位点成员和率先个递归成员。

  2. 例程的递归调用。

    递归调用包含四个或多个由援引 CTE 本身的 UNION ALL 运算符联接的
    CTE_query_definitions。这几个查询定义被称之为“递归成员”。

  3. 悬停车检查查。

    停下检查是隐式的;当上叁个调用中未再次回到行时,递归将告一段落。

 

伪代码和语义

递归 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 作为输入,将 Ti+1
    作为出口。

  4. 双重步骤 3,直到回到空集。

  5. 回去结果集。这是对 T0 到 Tn 执行 UNION ALL
    的结果。

 

实例

-- 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'Snchez', 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