SQL Server CTE和递归示例 [英] SQL Server CTE and recursion example
问题描述
我从不使用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.
第二个查询反复触发，查询员工所在的数据库
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中选择n
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...
)
问题2）现在关于CTE和员工关系递归
当我添加两个经理，并在第二个经理下添加了几个员工之后，问题就开始了。
i要显示第一位经理详细信息，而在接下来的行中，只有那些员工详细信息才会出现在该经理的下属中
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
Another one sql with tree structure
SELECT ID,space(nLevel+
(CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
)+Name
FROM Hierarchy
ORDER BY Family, nLevel
这篇关于SQL Server CTE和递归示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
