SQL CTE递归：返回父记录 [英] SQL CTE Recursion: Returning Parent Records

问题描述

我目前正在运行CTE查询，以从employees表中递归构建雇员层次结构，这与大多数递归示例所展示的类似。我受困的地方是，我试图查询单个雇员并检索其上方的层次结构。下面是我尝试使用的表的示例：

I am currently running a CTE query to recursively build an employee hierarchy from an employees table similar to what most recursive examples demonstrate. Where I am stuck is that I am trying to query for a single employee and retrieve the hierarchy above him. Below is an example of the table I am trying to work with:

Employees
===========================================================================
EmployeeID    MgrID    Name
1             null     Joe
2             1        John
3             2        Rob
4             2        Eric

以下是允许我从上至下显示层次结构的SQL：

The following is the SQL that allows me to display the hierarchy from the top down:

with employeeMaster as (
    select p.EmployeeID, p.MgrID, p.Name
    from Employees p
    where p.MgrID is null

    union all

    select c.EmployeeID, c.MgrID, c.Name
    from employeeMaster cte inner join Employees c on c.MgrID = cte.EmployeeID
)
select * from employeeMaster

我遇到的问题是我无法弄清楚如何查询最低级别的员工Rob或Eric，并从Joe返回他之上的层次结构>约翰>埃里克。似乎这应该很容易，但我无法发现它。

Where I am stuck is that I can't figure out how to query for the lowest level employee, either Rob or Eric, and return the hierarchy above him from Joe > John > Eric. It seems as though this should be easy but I can't spot it for the life of me.

推荐答案

您在寻找吗？一个查询来返回可变数量的列，具体取决于层次结构的深度？还是仅在一个字段中使用连接字符串？

Are you looking for a query to return a variable number of columns, depending on the depth of hierarchy? Or just a concatenated string in one field?

这是对您的查询的一个小更改，它将使Eric和层次结构中位于他上方的任何人都获得帮助。

Here's a minor change to your query that will get Eric and anyone above him in the hierarchy.

WITH    employeeMaster
      AS ( SELECT   p.EmployeeID ,
                    p.MgrID ,
                    p.NAME
           FROM     Employees p
           WHERE    p.NAME = 'Eric'
           UNION ALL
           SELECT   c.EmployeeID ,
                    c.MgrID ,
                    c.NAME
           FROM     employeeMaster cte
                    INNER JOIN Employees c ON c.EmployeeID = cte.MgrID
         )
SELECT  *
FROM    employeeMaster m

这篇关于SQL CTE递归：返回父记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！