SQL Server CTE和递归示例

Question

SQL Server CTE和递归示例我从不在递归中使用CTE。我只是在读一篇关于它的文章。本文在SQLServerCTE和递归的帮助下展示了员工信息。它基本上是向员工和他们的经理展示信息。我无法理解这个查询是如何工作的。以下是查询：WITH  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)  AS  (    SELECT EmployeeID, FirstName, LastName, ManagerID, 1    FROM Employees    WHERE ManagerID IS NULL    UNION ALL    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,      r.EmpLevel + 1    FROM Employees e      INNER JOIN cteReports r        ON e.ManagerID = r.EmpID  )SELECT  FirstName + ' ' + LastName AS FullName,  EmpLevel,  (SELECT FirstName + ' ' + LastName FROM Employees    WHERE EmployeeID = cteReports.MgrID) AS ManagerFROM cteReportsORDER BY EmpLevel, MgrID在这里，我发布了关于输出是如何显示的：enter image description here我只需要知道它是如何首先向经理展示，然后在循环中展示他的下属。我猜第一个SQL语句只触发一次，并返回所有员工ID。第二个查询重复触发，使用当前管理器id查询Employee所在的数据库。请解释SQL语句如何在内部循环中执行，并告诉我SQL执行顺序。谢谢。我的第二阶段问题;WITH Numbers AS(    SELECT n = 1    UNION ALL    SELECT n + 1    FROM Numbers    WHERE n+1 <= 10)SELECT nFROM Numbers问题1)N的值是如何增加的？如果每次给N赋值，则N值可以增加，但只有第一次初始化N值。Q2)CTE和雇员关系的递归：当我增加两个经理，在第二个经理下面再增加几个员工的时候，问题就开始了。我希望显示第一个经理详细信息，并在下一行中只显示与该经理下属相关的员工详细信息。假设ID     Name      MgrID    Level---    ----      ------   -----1      Keith      NULL     12      Josh       1        23      Robin      1        24      Raja       2        35      Tridip     NULL     16      Arijit     5        27      Amit       5        28      Dev        6        3我想用CTE表达式以这种方式显示结果。请告诉我如何修改我的SQL，我在这里给出的，以拉经理-雇员关系。谢谢。我希望输出是这样的：ID          Name   MgrID       nLevel      Family----------- ------ ----------- ----------- --------------------1           Keith  NULL        1           13           Robin  1           2           12           Josh   1           2           14           Raja   2           3           15           Tridip NULL        1           27           Amit   5           2           26           Arijit 5           2           28           Dev    6           3           2这有可能.？

Answer 1

我还没有测试您的代码，只是试图帮助您了解它是如何在注释中运行的；

WITH

  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)

  AS

  (

-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>

-- In a rCTE, this block is called an [Anchor]

-- The query finds all root nodes as described by WHERE ManagerID IS NULL

    SELECT EmployeeID, FirstName, LastName, ManagerID, 1

    FROM Employees

    WHERE ManagerID IS NULL

-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>

    UNION ALL

-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>    

-- This is the recursive expression of the rCTE

-- On the first "execution" it will query data in [Employees],

-- relative to the [Anchor] above.

-- This will produce a resultset, we will call it R{1} and it is JOINed to [Employees]

-- as defined by the hierarchy

-- Subsequent "executions" of this block will reference R{n-1}

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,

      r.EmpLevel + 1

    FROM Employees e

      INNER JOIN cteReports r

        ON e.ManagerID = r.EmpID

-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>

  )

SELECT

  FirstName + ' ' + LastName AS FullName,

  EmpLevel,

  (SELECT FirstName + ' ' + LastName FROM Employees

    WHERE EmployeeID = cteReports.MgrID) AS Manager

FROM cteReports

ORDER BY EmpLevel, MgrID

递归的最简单例子CTE我可以想到说明它的运作是什么；

;WITH Numbers AS

(

    SELECT n = 1

    UNION ALL

    SELECT n + 1

    FROM Numbers

    WHERE n+1 <= 10

)

SELECT n

FROM Numbers

Q1)N的值是如何增加的。如果每次都将值赋值给N，则N值可以增加，但只有第一次初始化N值时才能增加N值。.

A1:在这种情况下，N不是变量。N是化名。它相当于SELECT 1 AS N..这是一种个人偏好的语法。中有两种主要的混叠列方法。CTE在……里面T-SQL..我包括了一个简单的CTE在……里面Excel以一种更熟悉的方式来说明正在发生的事情。

--  Outside

;WITH CTE (MyColName) AS

(

    SELECT 1

)

-- Inside

;WITH CTE AS

(

    SELECT 1 AS MyColName

    -- Or

    SELECT MyColName = 1  

    -- Etc...

)

Excel_CTE

问题2)在这里，关于CTE和员工关系的递归，当我在第二个经理下面增加两个经理，再增加几个员工，然后开始问题。我想显示第一个经理详细信息，在接下来的行中，只有那些员工详细信息才会出现那些从属于该经理的员工详细信息。

A2:

这个密码能回答你的问题吗？

--------------------------------------------

-- Synthesise table with non-recursive CTE

--------------------------------------------

;WITH Employee (ID, Name, MgrID) AS 

(

    SELECT 1,      'Keith',      NULL   UNION ALL

    SELECT 2,      'Josh',       1      UNION ALL

    SELECT 3,      'Robin',      1      UNION ALL

    SELECT 4,      'Raja',       2      UNION ALL

    SELECT 5,      'Tridip',     NULL   UNION ALL

    SELECT 6,      'Arijit',     5      UNION ALL

    SELECT 7,      'Amit',       5      UNION ALL

    SELECT 8,      'Dev',        6   

)

--------------------------------------------

-- Recursive CTE - Chained to the above CTE

--------------------------------------------

,Hierarchy AS

(

    --  Anchor

    SELECT   ID

            ,Name

            ,MgrID

            ,nLevel = 1

            ,Family = ROW_NUMBER() OVER (ORDER BY Name)

    FROM Employee

    WHERE MgrID IS NULL

    UNION ALL

    --  Recursive query

    SELECT   E.ID

            ,E.Name

            ,E.MgrID

            ,H.nLevel+1

            ,Family

    FROM Employee   E

    JOIN Hierarchy  H ON E.MgrID = H.ID

)

SELECT *

FROM Hierarchy

ORDER BY Family, nLevel

另一个具有树结构的SQL

SELECT ID,space(nLevel+

                    (CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)

                )+Name

FROM Hierarchy

ORDER BY Family, nLevel

Answer 2

我想概述一个简短的语义平行，一个已经正确的答案。

在“简单”术语中，递归CTE可以从语义上定义为以下部分：

1：CTE查询。也叫锚。

2：在(1)中对CTE进行递归的CTE查询，使用UNIONALL(或UNION或UNITE或INTERSECT)，因此最终结果将相应地返回。

3：拐角处/终止条件。默认情况下，当递归查询返回的行/元组不多时。

下面是一个简短的例子，可以清楚地说明情况：

;WITH SupplierChain_CTE(supplier_id, supplier_name, supplies_to, level)

AS

(

SELECT S.supplier_id, S.supplier_name, S.supplies_to, 0 as level

FROM Supplier S

WHERE supplies_to = -1    -- Return the roots where a supplier supplies to no other supplier directly

UNION ALL

-- The recursive CTE query on the SupplierChain_CTE

SELECT S.supplier_id, S.supplier_name, S.supplies_to, level + 1

FROM Supplier S

INNER JOIN SupplierChain_CTE SC

ON S.supplies_to = SC.supplier_id

)

-- Use the CTE to get all suppliers in a supply chain with levels

SELECT * FROM SupplierChain_CTE

说明：第一个CTE查询返回没有直接提供给任何其他供应商的基本供应商(如LEAS)(-1)。

第一次迭代中的递归查询将获取向锚返回的供应商提供服务的所有供应商。这个过程一直持续到条件返回元组为止。

UNION All在全部递归调用中返回所有元组。

另一个很好的例子可以找到。这里.

PS：要使递归CTE工作，关系必须具有要处理的分层(递归)条件。元素=元素.。你说对了。