SQL：在同一个表上构建层次结构和嵌套查询

I am trying to build hierarchies by nesting queries on the same table in MS SQL Server 2014. To give an example of what I am trying to achieve:

我试图通过在MS SQL Server 2014中的同一个表上嵌套查询来构建层次结构。举例说明我想要实现的目标：

I have a table 'employees' whith the following columns:

我有一个表“员工”，其中包含以下列：

[ID],[First Name],[Last Name],[ReportsTo]

{1},{John},{Doe},{2}

{2},{Mary},{Miller},{NULL}

I am trying to build a statement, where I join the employees table with itself and where I build a hierarchy with the boss on top.

我正在尝试构建一个语句，我在其中加入了employees表，并在那里与老板建立了一个层次结构。

Expected Result:

预期结果：

[Employee],[Boss]

{Miller,Mary},{NULL}

{Doe, John},{Miller,Mary}

I apologize, if this is a stupid question, but I fail to create a working nested query.

我很抱歉，如果这是一个愚蠢的问题，但我无法创建一个有效的嵌套查询。

Could you please help me with that?

你能帮帮我吗？

Thank you very much in advance

非常感谢你提前

1 个解决方案

#1

Based on the intended results, it looks like what you essentially want is a list of employees. So let's start with that:

根据预期的结果，您看起来像是一个员工列表。那么让我们开始吧：

SELECT LastName, FirstName, ReportsTo FROM Employees

This gives you the list, so you now have the objects you're looking for. But you need to fill out more data. You want to follow ReportsTo and show data from the record to which that points as well. This would be done exactly as it would if the foreign key pointed to a different table. (The only difference from being the same table is that you must use table aliases in the query, since you're including the same table twice.)

这为您提供了列表，因此您现在拥有了您正在寻找的对象。但是你需要填写更多数据。您希望关注ReportsTo并显示指向该记录的记录中的数据。这将完全像外键指向不同的表时那样完成。（与同一个表的唯一区别在于您必须在查询中使用表别名，因为您要包含两次相同的表。）

So let's start by joining the table:

那么让我们从加入表开始吧：

SELECT e.LastName, e.FirstName, e.ReportsTo
FROM Employees e
  LEFT OUTER JOIN Employees b on e.ReportsTo = b.ID

The results should still be the same, but now you have more data to select from. So you can add the new columns to the SELECT clause:

结果应该仍然相同，但现在您有更多数据可供选择。因此，您可以将新列添加到SELECT子句：

SELECT
  e.LastName AS EmployeeLastName,
  e.FirstName AS EmployeeFirstName,
  b.LastName AS BossLastName,
  b.FirstName AS BossFirstName
FROM Employees e
  LEFT OUTER JOIN Employees b on e.ReportsTo = b.ID

It's a join like any other, it just happens to be a join to the same table.

这是一个像其他任何一样的连接，它恰好是对同一个表的连接。

#1