使用CTE公用表表达式的递归查询(WITH AS)上海时

当有些查询援用递归 CTE 时,它即被喻为递归查询。递归查询普通用于重临分层数据,比如:显示有个别团体图中的雇员或物品清单方案(在那之中父级产品有三个或八个零部件,而那么些组件恐怕还应该有子组件,大概是任何父级产品的零部件)中的数据。

实例

-- 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

 

 

递归 CTE 能够十分的大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运作递归查询所需的代码。在 SQL Server 的开始时期版本中,递归查询普通要求运用有时表、游标和逻辑来决定递归步骤流。

 

递归 CTE 的结构

Transact-SQL 中的递归 CTE 的结构与任何编制程序语言中的递归例程相似。即使其余语言中的递归例程再次来到标量值,但递归 CTE 能够回到多行。

递归 CTE 由下列多个成分结合:

  1. 例程的调用。

    递归 CTE 的率先个调用包涵四个或五个由 UNION ALL、UNION、EXCEPT 或 INTEMuranoSECT 运算符联接的 CTE_query_definitions。由于那么些查询定义产生了 CTE 结构的标准结果集,所以它们被誉为“定位点成员”。

    CTE_query_definitions 被视为定位点成员,除非它们引用了 CTE 自身。全体定位点成员查询定义必需放置在率先个递归成员定义在此之前,並且必须采纳UNION ALL 运算符联接最终一个定位点成员和第一个递归成员。

  2. 例程的递归调用。

    递归调用包罗二个或两个由援用 CTE 自身的 UNION ALL 运算符联接的 CTE_query_definitions。那几个查询定义被誉为“递归成员”。

  3. 终止检查。

    截至检查是隐式的;当上贰个调用中未重回行时,递归将终止。

 

公用表表明式 (CTE) 具备二个第一的帮助和益处,那正是能够引用其本身,进而成立递归 CTE。递归 CTE 是三个再一次施行开端 CTE 以回到数据子集直到获取完整结果集的公用表表明式。

伪代码和语义

递归 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 的结果。

 

本文由上海时时乐走势图发布于上海时时乐走势图官网,转载请注明出处:使用CTE公用表表达式的递归查询(WITH AS)上海时

您可能还会对下面的文章感兴趣: