递归CTE概念混乱 [英] Recursive CTE Concept Confusion

问题描述

我试图理解在我的SQL代码中使用CTE的概念。我已经在许多在线文章中解释了这个概念，但是我无法掌握如何迭代来呈现层次结构数据。解释R-CTE的广泛使用的示例之一是Employee and ManagerID示例，如下所示：

I am trying to understand the concepts of using CTE in my SQL code. I have gone through a number of online posts explaining the concept but I cannot grasp how it iterates to present the hierarchical data. One of the widely used examples to explain the R-CTE is the Employee and ManagerID Example as below:

USE AdventureWorks
GO
WITH Emp_CTE AS (
  SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
  FROM HumanResources.Employee
  WHERE ManagerID IS NULL

  UNION ALL

  SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
  FROM HumanResources.Employee e
  INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

锚点查询将获取经理。之后，如果递归查询一次又一次地调用锚查询，并且锚查询只有一个记录即经理，那么我将无法理解其他员工。

The anchor query will grab the manager. After that I can't understand how it would bring the other employees if the recursive query is calling the anchor query again and again and the anchor query just has a single record which is the manager.

推荐答案

因此您想了解递归CTE。

So you want to understand a recursive CTE.

这真的很简单。

首先是种子查询，它可以获取原始记录。

First there's the seed query which gets the original records.

  SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
  FROM HumanResources.Employee
  WHERE ManagerID IS NULL

在您的情况下，这是没有经理的雇员。

成为老板

In your case it's the employees without a manager.
Which would be the boss(es)

以简化示例进行演示：

EmployeeID LoginID ManagerID Title 
---------- ------- --------- ------------
101        boss    NULL      The Boss

第二个查询查找具有先前记录为经理的员工。

The second query looks for employees that have the previous record as a manager.

  SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
  FROM HumanResources.Employee e
  INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID

由于它是递归CTE，因此CTE在第二个查询中使用它自己。

您可以将其视为循环，在该循环中，它使用先前的记录来获取下一个记录。

Since it's a recursive CTE, the CTE uses itself in the second query.
You could see it as a loop, where it uses the previous records to get the next.

对于该递归循环的第一个迭代，您可能会得到以下内容：

For the first iteration of that recursive loop you could get something like this:

 EmployeeID LoginID ManagerID Title 
---------- ------- --------- ------------
102        head1    101      Top Manager 1
103        head2    101      Top Manager 2

在第二次迭代中，它将使用该第一次迭代中的记录来查找下一个。

For the second iteration it would use the records from that first iteration to find the next.

 EmployeeID LoginID ManagerID Title 
---------- ------- --------- ------------

104        bob     102       Department Manager 1
105        hilda   102       Department Manager 2

108        john    103       Department Manager 4
109        jane    103       Department Manager 5

第三次迭代，它将使用第二次迭代中的记录。

For the 3th iteration it would use the records from the 2nd iteration.

...

这一直持续到没有更多的员工加入ManagerID为止

And this continues till there are no more employees to join on the ManagerID

然后，在所有循环之后，CTE将返回通过所有这些迭代找到的所有记录。

Then after all the looping, the CTE will return all the records that were found through all those iterations.

这篇关于递归CTE概念混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！