SQL Server CTE 和递归示例

SQL Server CTE and recursion example(SQL Server CTE 和递归示例)

问题描述

我从不将 CTE 与递归一起使用.我只是在读一篇关于它的文章.本文借助 Sql server CTE 和递归显示员工信息.它基本上显示员工及其经理的信息.我无法理解这个查询是如何工作的.这是查询:

I never use CTE with recursion. I was just reading an article on it. This article shows employee info with the help of Sql server CTE and recursion. It is basically showing employees and their manager info. I am not able to understand how this query works. Here is the query:

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

我在这里发布关于输出如何显示的信息:

Here I am posting about how the output is showing:

我只需要知道它是如何在循环中先显示经理然后显示他的下属的.我猜第一个 sql 语句只触发一次并返回所有员工 ID.

I just need to know how it is showing manager first and then his subordinate in a loop. I guess the first sql statement fires only once and that returns all employee ids.

第二个查询重复触发，查询员工所在的数据库，并使用当前经理 ID.

And the second query repeatedly fires, querying the database on which employee exists with the current manager id.

请说明sql语句是如何在内部循环中执行的，并告诉我sql执行顺序.谢谢.

Please explain how the sql statement executes in an internal loop and also tell me the sql execution order. Thanks.

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

Q 1) N 的值是如何递增的?如果每次都将值分配给 N，则 N 值可以递增，但仅限于第一次初始化 N 值.

Q 1) how is the value of N is getting incremented? if the value is assigned to N every time then N value can be incremented but only the first time N value was initialized.

Q 2) CTE 和员工关系的递归:

Q 2) CTE and recursion of employee relations:

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

The moment I add two managers and add a few more employees under the second manager is where the problem starts.

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

I want to display the first manager detail and in the next rows only those employee details that relate to the subordinate of that manager.

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 中要修改什么，以便拉取经理与员工的关系.谢谢.

I want to display the results in such way with CTE expressions. Please tell me what to modify in my sql which I gave here in order to pull manager-employee relations. Thanks.

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

这可能吗……?

推荐答案

我没有测试你的代码，只是想帮助你理解它在评论中是如何运作的；

I haven't tested your code, just tried to help you understand how it operates in comment;

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示例来说明其操作是；

The simplest example of a recursive CTE I can think of to illustrate its operation is;

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

Q 1) N 的值是如何递增的.如果每次都将值分配给 N，则 N 值可以递增，但仅在第一次初始化 N 值时.

A1: 在这种情况下，N 不是变量.N 是一个别名.它相当于 SELECT 1 AS N.这是个人喜好的语法.在 T-SQL 中的 CTE 中有两种主要的列别名方法.我在 Excel 中包含了一个简单的 CTE 的模拟，以尝试以更熟悉的方式说明正在发生的事情.

A1: In this case, N is not a variable. N is an alias. It is the equivalent of SELECT 1 AS N. It is a syntax of personal preference. There are 2 main methods of aliasing columns in a CTE in T-SQL. I've included the analog of a simple CTE in Excel to try and illustrate in a more familiar way what is happening.

--  Outside
;WITH CTE (MyColName) AS
(
    SELECT 1
)
-- Inside
;WITH CTE AS
(
    SELECT 1 AS MyColName
    -- Or
    SELECT MyColName = 1  
    -- Etc...
)

Q 2) 现在这里是关于 CTE 和员工关系递归的当我添加两个经理并在第二个经理下添加更多员工时，问题就开始了.我想显示第一个经理的详细信息，在接下来的行中，只有那些员工的详细信息才会出现在该经理的下属

A2:

此代码是否回答了您的问题?

Does this code answer your question?

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

这篇关于SQL Server CTE 和递归示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！