递归CTE在SQL Server中如何工作？

本文介绍了递归CTE在SQL Server中如何工作？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 有人可以帮助我理解此递归CTE的工作原理吗？ 其中 RECURSIVECTE（EMPID，FULLNAME，MANAGERID ，[ORGLEVEL]）AS （SELECT EMPID， FULLNAME， MANAGERID， 1 来自RECURSIVETBL MANAGERID为空 UNION所有 SELECT A.EMPID， A.FULLNAME， A.MANAGERID， B. [ORGLEVEL] + 1 来自RECURSIVETBL A 加入RECURSIVECTE B ON A.MANAGERID = B.EMPID）选择* 来自递归 解决方案 SQL Server中的递归CTE由两部分组成： 锚点：是递归的起点。此集合将通过递归联接进一步扩展。 选择 EMPID， FULLNAME， MANAGERID， 1 AS ORGLEVEL 从 RECURSIVETBL WHERE MANAGERID为空 似乎正在获取所有没有经理的员工（将是高层领导者，还是来自树型关系的根源）。 递归：与 UNION ALL 链接时，该集合必须引用声明的CTE（因此使它递归）。认为它是如何扩展锚点的结果的下一个层次。 UNION ALL 选择 A.EMPID， A.FULLNAME， A.MANAGERID， B. [ORGLEVEL] + 1 从 RECURSIVETBL A JOIN RECURSIVECTE B –注意，我们引用的是 RECURSIVECTE，即CTE，我们在A.MANAGERID = B.EMPID $ p> 在此示例中，我们正在获取（第一次迭代）锚定结果集（所有没有经理的员工），并将它们与 RECURSIVETBL 到 MANAGERID ，因此 A.EMPID 将容纳先前选择的经理的员工。只要每个最后一个结果集都可以生成新行，这种连接就会持续下去。 对递归部分的内容有一些限制（不分组）或其他嵌套的递归）。此外，由于它以 UNION ALL 开头，因此它的规则也适用（列的数量和数据类型必须匹配）。 关于 ORGLEVEL ，它以设置为1的锚点开始（在此处进行硬编码）。在递归集上进行进一步扩展时，它将获取上一个集（锚，在第一次迭代中），并添加1，因为它的表达式是 B. [ORGLEVEL] + 1 ，其中 B 是上一组。这意味着它以1（最高领导者）开头，并且为每个后代不断增加1，从而代表了组织的所有级别。 找到员工时在 ORGLEVEL = 3 的情况下，他在他上方有2位经理。 逐步介绍工作示例 让我们遵循以下示例： EmployeeID ManagerID 1空 2 1 3 1 4 2 5 2 6 1 7 6 8 6 9空 10 3 11 3 12 10 13 9 14 9 15 13 锚点：员工没有管理员（ ManagerID为NULL ）。这将从公司的所有头号坏蛋开始。至关重要的是要注意，如果锚集为空，则整个递归CTE将为空，因为没有起点，也没有要加入的递归集。 SELECT EmployeeID = E.EmployeeID， ManagerID = NULL，-在WHERE过滤器中始终为空 HierarchyLevel = 1， HierarchyRoute = CONVERT （VARCHAR（MAX），E.EmployeeID）来自员工为AS E WHERE E.ManagerID为NULL 其中有哪些： EmployeeID ManagerID HierarchyLevel HierarchyRoute 1（空）1 1 9（空）1 9 递归N°1 ：使用此 UNION ALL 递归： UNION ALL SELECT EmployeeID = E.EmployeeID， ManagerID = E.ManagerID， HierarchyLevel = R.HierarchyLevel + 1， HierarchyRoute = R.HierarchyRoute +’-> '+ CONVERT（VARCHAR（10），E.EmployeeID）从递归CTE AS R 内加入员工E E ON R.EmployeeID = E.ManagerID 为此 INNER JOIN ， RecursiveCTE 有2行（锚集），员工ID为 1 和 9 。因此，此 JOIN 实际上将返回此结果。 HierarchyLevel EmployeeID ManagerID HierarchyRoute 2 2 1 1-> 2 2 3 1 1-> 3 2 6 1 1-> 6 2 13 9 9-> 13 2 14 9 9-> 14 查看 HierarchyRoute 如何从1开始和9并移动到每个后代？我们还将 HierarchyLevel 增加了1。 因为结果由 UNION ALL链接，此时我们得到以下结果（步骤1 + 2）： HierarchyLevel EmployeeID ManagerID层次结构路由 1 1（空）1 1 9（空）9 2 2 1 1-> 2 2 3 1 1-> 3 2 6 1 1-> 6 2 13 9 9-> 13 2 14 9 9-> 14 这是棘手的部分，对于以下每个迭代，递归引用 RecursiveCTE 仅包含最后一个迭代结果集，而不包含累积的结果集。这意味着对于下一次迭代， RecursiveCTE 将代表以下行： HierarchyLevel EmployeeID ManagerID HierarchyRoute 2 2 1 1-> 2 2 3 1 1-> 3 2 6 1 1-> 6 2 13 9 9-> 13 2 14 9 9-> 14 递归N °2 ：遵循相同的递归表达式... UNION ALL SELECT EmployeeID = E.EmployeeID， ManagerID = E.ManagerID， HierarchyLevel = R.HierarchyLevel + 1， HierarchyRoute = R.HierarchyRoute +'-> '+ CONVERT（VARCHAR（10），E.EmployeeID）从递归CTE AS R 内加入员工E E ON R.EmployeeID = E.ManagerID 并考虑到在此步骤中 RecursiveCTE 仅保存 HierarchyLevel = 2 的行，如果此JOIN为以下（第3级！），则返回结果： HierarchyLevel EmployeeID ManagerID HierarchyRoute 3 4 2 1-> 2-> 4 3 5 2 1-> 2-> 5 3 7 6 1-> 6-> 7 3 8 6 1-> 6-> 8 3 10 3 1-> 3-> 10 3 11 3 1-> 3-> 11 3 15 13 9-> 13-> 15 此集合（仅此！）将在以下递归步骤中用作 RecursiveCTE ，它将被添加到累计总计中，现在是： HierarchyLevel EmployeeID ManagerID HierarchyRoute 1 1（null）1 1 9（null）9 2 2 1 1-> 2 2 3 1 1-> 3 2 6 1 1-> 6 2 13 9 9-> 13 2 14 9 9-> 14 3 4 2 1-> 2-> 4 3 5 2 1-> 2-> 5 3 7 6 1-> 6-> 7 3 8 6 1-> 6-> 8 3 10 3 1-> 3-> 10 3 11 3 1-> 3-> 11 3 15 13 9-> 13-> 15 递归N °3 ：从工作集中的3s级开始，加入的结果为： HierarchyLevel EmployeeID ManagerID层次路线 4 12 10 1-> 3-> 10-> 12 这成为我们下一个递归的工作集 递归N°4 ：从上一个唯一的行级别4开始步骤，联接的结果不产生任何行（没有雇员将EmployeeID 12作为ManagerID）。不返回任何行表示迭代结束。 最终结果集很高： HierarchyLevel EmployeeID ManagerID HierarchyRoute 1 1（null）1 1 9（null）9 2 2 1 1-> 2 2 3 1 1-> 3 2 6 1 1-> 6 2 13 9 9-> 13 2 14 9 9-> 14 3 4 2 1-> 2-> 4 3 5 2 1-> 2-> 5 3 7 6 1-> 6-> 7 3 8 6 1-> 6-> 8 3 10 3 1-> 3-> 10 3 11 3 1-> 3-> 11 3 15 13 9-> 13-> 15 4 12 10 1-> 3-> 10-> 12 这是完整的小提琴和代码： 创建表Employee（EmployeeID INT ，ManagerID INT） 插入员工（EmployeeID，ManagerID）值（1，NULL），（2，1），（ 3，1），（4，2），（5，2），（6，1），（7，6），（ 8，6），（9，NULL），（10，3），（11，3），（12，10），（ 13，9），（14，9），（15，13） RecursiveCTE AS （选择 EmployeeID = E.EmployeeID， ManagerID = NULL，-通过WHERE过滤器始终为null HierarchyLevel = 1， HierarchyRoute = CONVERT（VARCHAR（MAX），E.EmployeeID） FROM 员工AS E WHERE E.ManagerID为空 UNION ALL SELECT EmployeeID = E.EmployeeID， ManagerID = E.Manag erID， HierarchyLevel = R.HierarchyLevel + 1， HierarchyRoute = R.HierarchyRoute +’-> '+ CONVERT（VARCHAR（10），E.EmployeeID）从递归CTE AS R 内加入员工E E ON R.EmployeeID = E.ManagerID ）选择 R.HierarchyLevel， R.EmployeeID， R.ManagerID， R.HierarchyRoute 从递归CTE AS R 订购按 R.HierarchyLevel， R.EmployeeID Can anyone help me understand how this recursive CTE works?WITHRECURSIVECTE (EMPID, FULLNAME, MANAGERID, [ORGLEVEL]) AS (SELECT EMPID, FULLNAME, MANAGERID, 1 FROM RECURSIVETBL WHERE MANAGERID IS NULL UNION ALL SELECT A.EMPID, A.FULLNAME, A.MANAGERID, B.[ORGLEVEL] + 1 FROM RECURSIVETBL A JOIN RECURSIVECTE B ON A.MANAGERID = B.EMPID)SELECT *FROM RECURSIVECTE; 解决方案 Recursive CTEs in SQL Server have 2 parts:The Anchor: Is the starting point of your recursion. It's a set that will be further expanded by recursive joins. SELECT EMPID, FULLNAME, MANAGERID, 1 AS ORGLEVELFROM RECURSIVETBLWHERE MANAGERID IS NULLIt seems that it's fetching all employees that don't have any managers (would be the top bosses, or roots from tree relationships).The recursion: Linked with a UNION ALL, this set has to reference the declaring CTE (thus making it recursive). Think of it as how will you expand the result of the anchor with the next level.UNION ALLSELECT A.EMPID, A.FULLNAME, A.MANAGERID, B.[ORGLEVEL] + 1FROM RECURSIVETBL A JOIN RECURSIVECTE B -- Notice that we are referencing "RECURSIVECTE" which is the CTE we are declaring ON A.MANAGERID = B.EMPIDOn this example, we are fetching (on a first iteration) the anchor result set (all employees with no managers) and joining them with RECURSIVETBL through MANAGERID, so A.EMPID will hold the employee of the previously selected manager. This joining goes on and on as long as each last result set can generate new rows.There are a few limitations on what you can put on the recursive part (no grouping or another nested recursion for example). Also, as it's preceeded with a UNION ALL, it's rules also apply (amount of columns and data types must match).About the ORGLEVEL, it starts with the anchor set at 1 (it's hard-coded there). When it's further expanded on the recursion set, it fetches the previous set (the anchor, on the first iteration) and it adds 1, as it's expression is B.[ORGLEVEL] + 1 with B being the previous set. This means that it starts with 1 (the top bosses) and it keeps adding 1 for each descendant, thus representing all the levels of the organization.When you find an employee at ORGLEVEL = 3 means that he has 2 managers over him.Step by step with working exampleLet's follow this example:EmployeeID ManagerID1 NULL2 13 14 25 26 17 68 69 NULL10 311 312 1013 914 915 13Anchor: Employees without managers (ManagerID IS NULL). This will start with all the top badass of your company. It's crucial to note that if the anchor set is empty, then the whole recursive CTE will be empty, as there is no starting point and no recursive set to join to.SELECT EmployeeID = E.EmployeeID, ManagerID = NULL, -- Always null by WHERE filter HierarchyLevel = 1, HierarchyRoute = CONVERT(VARCHAR(MAX), E.EmployeeID)FROM Employee AS EWHERE E.ManagerID IS NULLWhich are these:EmployeeID ManagerID HierarchyLevel HierarchyRoute1 (null) 1 19 (null) 1 9Recursion N°1: Using this UNION ALL recursion:UNION ALLSELECT EmployeeID = E.EmployeeID, ManagerID = E.ManagerID, HierarchyLevel = R.HierarchyLevel + 1, HierarchyRoute = R.HierarchyRoute + ' -> ' + CONVERT(VARCHAR(10), E.EmployeeID)FROM RecursiveCTE AS R INNER JOIN Employee AS E ON R.EmployeeID = E.ManagerIDFor this INNER JOIN, RecursiveCTE has 2 rows (the anchor set), with employees ID 1 and 9. So this JOIN will actually return this result.HierarchyLevel EmployeeID ManagerID HierarchyRoute2 2 1 1 -> 22 3 1 1 -> 32 6 1 1 -> 62 13 9 9 -> 132 14 9 9 -> 14See how the HierarchyRoute starts with 1 and 9 and moves to each descendant? We also increased HierarchyLevel by 1.Because the results are linked by a UNION ALL, at this point we have the following results (step 1 + 2):HierarchyLevel EmployeeID ManagerID HierarchyRoute1 1 (null) 11 9 (null) 92 2 1 1 -> 22 3 1 1 -> 32 6 1 1 -> 62 13 9 9 -> 132 14 9 9 -> 14Here is the tricky part, for each of the following iterations, recursive references to RecursiveCTE will only contain the last iteration result set, and not the accumulated set. This means that for the next iteration, RecursiveCTE will represent these rows:HierarchyLevel EmployeeID ManagerID HierarchyRoute2 2 1 1 -> 22 3 1 1 -> 32 6 1 1 -> 62 13 9 9 -> 132 14 9 9 -> 14Recursion N°2: Following the same recursive expression...UNION ALLSELECT EmployeeID = E.EmployeeID, ManagerID = E.ManagerID, HierarchyLevel = R.HierarchyLevel + 1, HierarchyRoute = R.HierarchyRoute + ' -> ' + CONVERT(VARCHAR(10), E.EmployeeID)FROM RecursiveCTE AS R INNER JOIN Employee AS E ON R.EmployeeID = E.ManagerIDAnd considering that in this step RecursiveCTE only holds rows with HierarchyLevel = 2, then the result if this JOIN is the following (level 3!):HierarchyLevel EmployeeID ManagerID HierarchyRoute3 4 2 1 -> 2 -> 43 5 2 1 -> 2 -> 53 7 6 1 -> 6 -> 73 8 6 1 -> 6 -> 83 10 3 1 -> 3 -> 103 11 3 1 -> 3 -> 113 15 13 9 -> 13 -> 15This set (and only this!) will be used in the following recursive step as RecursiveCTE, and it will be added to the accumulated grand total, which is now:HierarchyLevel EmployeeID ManagerID HierarchyRoute1 1 (null) 11 9 (null) 92 2 1 1 -> 22 3 1 1 -> 32 6 1 1 -> 62 13 9 9 -> 132 14 9 9 -> 143 4 2 1 -> 2 -> 43 5 2 1 -> 2 -> 53 7 6 1 -> 6 -> 73 8 6 1 -> 6 -> 83 10 3 1 -> 3 -> 103 11 3 1 -> 3 -> 113 15 13 9 -> 13 -> 15Recursion N°3: Starting with level 3s in our working set, the result of the join is:HierarchyLevel EmployeeID ManagerID HierarchyRoute4 12 10 1 -> 3 -> 10 -> 12This becomes our working set for next recursive step.Recursion N°4: Starting with the only row level 4 from previous step, the result of the join yields no rows (no employee has EmployeeID 12 as ManagerID). Returning no rows marks the end of the iterations.The final result set stands tall:HierarchyLevel EmployeeID ManagerID HierarchyRoute1 1 (null) 11 9 (null) 92 2 1 1 -> 22 3 1 1 -> 32 6 1 1 -> 62 13 9 9 -> 132 14 9 9 -> 143 4 2 1 -> 2 -> 43 5 2 1 -> 2 -> 53 7 6 1 -> 6 -> 73 8 6 1 -> 6 -> 83 10 3 1 -> 3 -> 103 11 3 1 -> 3 -> 113 15 13 9 -> 13 -> 154 12 10 1 -> 3 -> 10 -> 12Here is the complete fiddle and code:CREATE TABLE Employee (EmployeeID INT, ManagerID INT)INSERT INTO Employee (EmployeeID, ManagerID)VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 2), (6, 1), (7, 6), (8, 6), (9, NULL), (10, 3), (11, 3), (12, 10), (13, 9), (14, 9), (15, 13)WITH RecursiveCTE AS( SELECT EmployeeID = E.EmployeeID, ManagerID = NULL, -- Always null by WHERE filter HierarchyLevel = 1, HierarchyRoute = CONVERT(VARCHAR(MAX), E.EmployeeID) FROM Employee AS E WHERE E.ManagerID IS NULL UNION ALL SELECT EmployeeID = E.EmployeeID, ManagerID = E.ManagerID, HierarchyLevel = R.HierarchyLevel + 1, HierarchyRoute = R.HierarchyRoute + ' -> ' + CONVERT(VARCHAR(10), E.EmployeeID) FROM RecursiveCTE AS R INNER JOIN Employee AS E ON R.EmployeeID = E.ManagerID)SELECT R.HierarchyLevel, R.EmployeeID, R.ManagerID, R.HierarchyRouteFROM RecursiveCTE AS RORDER BY R.HierarchyLevel, R.EmployeeID 这篇关于递归CTE在SQL Server中如何工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！