我从不将 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
Here I am posting about how the output is showing:
我只需要知道它如何循环地首先显示经理，然后显示他的下属。 我猜第一个 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 值。
Q 2）CTE和员工关系的递归：
当我添加两名经理并在第二名经理下添加更多员工时，问题就开始了。
我想显示第一个经理的详细信息，并在下一行中仅显示与该经理的下属相关的员工详细信息。
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
这可能吗...？
我没有测试你的代码，只是试图帮助你理解它是如何在评论中运行的；
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
Q 1) N 的值如何递增。如果每次都将值分配给 N，则 N 值可以递增，但仅在第一次初始化 N 值时.
A1:在这种情况下，N不是变量。N是一个别名。它相当于SELECT 1 AS N。这是个人喜好的语法。有两种主要方法可以对列进行别名设置：CTE in T-SQL。我已经包含了一个简单的模拟CTE in Excel尝试以更熟悉的方式说明正在发生的事情。
-- Outside
;WITH CTE (MyColName) AS
(
SELECT 1
)
-- Inside
;WITH CTE AS
(
SELECT 1 AS MyColName
-- Or
SELECT MyColName = 1
-- Etc...
)
Q 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
SELECT ID,space(nLevel+
(CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
)+Name
FROM Hierarchy
ORDER BY Family, nLevel