SQL Server CTE和递归示例

Question

SQL Server CTE和递归示例

125

我从未使用递归的公共表达式（CTE）功能，只是在阅读一篇关于它的文章。该文章借助 Sql server 的 CTE 和递归功能来展示员工信息，主要是展示员工及其经理的信息。我不太能理解这个查询是如何工作的。以下是查询：

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 Manager
FROM cteReports
ORDER BY EmpLevel, MgrID

我在这里发布一个关于输出显示的帖子： enter image description here

我只想知道它是如何在循环中先显示经理，然后再显示下属的。我猜第一个sql语句只会执行一次，并返回所有员工的id。

而第二个查询会重复执行，在当前经理id下查询数据库中存在哪些员工。

请解释内部循环中如何执行sql语句并告诉我sql执行顺序。谢谢。

我的第二个问题阶段

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

问题1：N的值是如何递增的？如果每次将值赋给N，则N的值可以递增，但仅在第一次初始化N的值时。

问题2：雇员关系的公共表达式和递归：

当我添加两个经理并在第二个经理下添加更多员工时，问题就开始了。

我想显示第一个经理的详细信息，并且在接下来的行中仅显示与该经理的下属相关的员工详细信息。

假设

ID     Name      MgrID    Level
---    ----      ------   -----
1      Keith      NULL     1
2      Josh       1        2
3      Robin      1        2
4      Raja       2        3
5      Tridip     NULL     1
6      Arijit     5        2
7      Amit       5        2
8      Dev        6        3

我希望使用CTE表达式以这种方式显示结果。请告诉我在我提供的SQL中需要修改什么，以拉取经理-员工关系。谢谢。

我希望输出如下：

ID          Name   MgrID       nLevel      Family
----------- ------ ----------- ----------- --------------------
1           Keith  NULL        1           1
3           Robin  1           2           1
2           Josh   1           2           1
4           Raja   2           3           1
5           Tridip NULL        1           2
7           Amit   5           2           2
6           Arijit 5           2           2
8           Dev    6           3           2

这是否可能？

- Thomas

https://learnsql.com/blog/sql-recursive-cte/ - Billu

4个回答

12

想要简要概述与已有正确答案类似的语义。

“简单”地说，递归CTE可以在以下几个部分中进行语义定义：

1：CTE查询。也称为ANCHOR。

2：CTE上的递归查询(1)使用UNION ALL（或UNION或EXCEPT或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查询返回不直接向任何其他供应商(-1)供应的基本供应商（类似于叶子）

第一次迭代中的递归查询获取所有向ANCHOR返回的供应商提供的供应商。这个过程继续进行，直到条件返回元组。

UNION ALL返回所有递归调用的元组。

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

PS：为了使递归CTE起作用，关系必须具有递归层次结构条件。例如：elementId = elementParentId..你懂得。

- Vaibhav

12

使用递归CTE执行过程非常令人困惑，我在 https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx 找到了最好的答案，CTE执行过程的摘要如下：

递归执行的语义如下：

将CTE表达式分成锚定成员和递归成员。
运行锚定成员以创建第一个调用或基本结果集(T0)。
使用Ti作为输入和Ti + 1作为输出运行递归成员。
重复步骤3，直到返回一个空集。
返回结果集。这是T0到Tn的联合（UNION ALL）。

- Pavan

-7

    --DROP TABLE #Employee
    CREATE TABLE #Employee(EmpId BIGINT IDENTITY,EmpName VARCHAR(25),Designation VARCHAR(25),ManagerID BIGINT)

    INSERT INTO #Employee VALUES('M11M','Manager',NULL)
    INSERT INTO #Employee VALUES('P11P','Manager',NULL)

    INSERT INTO #Employee VALUES('AA','Clerk',1)
    INSERT INTO #Employee VALUES('AB','Assistant',1)
    INSERT INTO #Employee VALUES('ZC','Supervisor',2)
    INSERT INTO #Employee VALUES('ZD','Security',2)


    SELECT * FROM #Employee (NOLOCK)

    ;
    WITH Emp_CTE 
    AS
    (
        SELECT EmpId,EmpName,Designation, ManagerID
              ,CASE WHEN ManagerID IS NULL THEN EmpId ELSE ManagerID END ManagerID_N
        FROM #Employee  
    )
    select EmpId,EmpName,Designation, ManagerID
    FROM Emp_CTE
    order BY ManagerID_N, EmpId

- Vishal Motwani

3

这是一个只包含代码的回答，甚至没有回答问题，因为其中没有递归CTE。 - Dragomok

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MarkD · Accepted Answer

我没有测试过你的代码，只是在评论中尝试帮助你理解它的操作方式。

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

问题 1) N 的值如何增加。如果每次将值分配给 N，那么可以增加 N 的值，但只有第一次 N 的值被初始化。

A1: 在这种情况下，N 不是一个变量。 N 是一个别名。它相当于 SELECT 1 AS N。这是个人偏好的语法。在 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

问题二）现在讲一下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