SQLServerCTE 和递归示例

我从不在递归中使用 CTE。我正在读一篇关于它的文章。本文在 Sql 服务器 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 的执行顺序。

MY 2nd phase of question

;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 值时递增。

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

Is this possible...?

214733 次浏览
小开
最佳答案

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

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 值初始化时 。

在这种情况下，N不是一个变量。N是个化名。它相当于 SELECT 1 AS N。这是一种个人偏好的句法。在 T-SQL的 CTE中，有两种主要的混淆列的方法。我已经在 Excel中加入了一个简单的 CTE的模拟，试图用一种更熟悉的方式来说明正在发生的事情。

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

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

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

用“简单”的术语来说，递归 CTE 可以在语义上定义为以下部分:

1: CTE 查询。也称为 ANCHOR。

2: 使用 UNION ALL (或 UNION、 EXCEPT 或 INTERSECT)对(1)中的 CTE 执行递归 CTE 查询，因此相应地返回最终结果。

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: For a recursive CTE to work, the relations must have a hierarchical (recursive) condition to work on. Ex: elementId = elementParentId.. you get the point.

小开

执行过程与递归 CTE 真的很混淆，我在 https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx中找到了最佳答案，CTE 执行过程的摘要如下。

The semantics of the recursive execution is as follows:

  1. 将 CTE 表达式拆分为定位点成员和递归成员。
  2. 运行锚成员，创建第一个调用或基本结果集(T0)。
  3. 以 Ti 作为输入，Ti + 1作为输出，运行递归元件。
  4. 重复步骤3，直到返回一个空集。
  5. 返回结果集。这是一个从 T0到 Tn 的 UNION ALL。
小开 
    --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

提交答案