显示层次数据的SQL查询

Question

我有两个表 - '用户' 和 '监管'

对于这个例子，我的用户表非常简单：-

Users
=====
ID (PK)
UserName

一些用户需要管理其他用户，因此我建立了第二个表格“Supervision”来管理这个过程：

Supervision
===========
UserID
SuperID - this is the ID of the staff member that the user supervises.

这个表用于将“用户”表自身连接起来，以确定特定用户的上级。可能会有一个用户有多个上级，因此这个表非常适合这个目的。

以下是“用户”表中的示例数据：-

userID  userName
1       Bob
2       Margaret
3       Amy
4       Emma
5       Carol
6       Albert
7       Robert
8       Richard
9       Harry
10      Arthur

我的“Supervision”数据如下：

userID  superID
1       2
1       3
2       4
2       5
3       4
3       5
6       1
6       7
7       8
7       9
9       10

如果我想查看直接向Bob汇报的人，编写SQL查询很简单，并告诉我Margaret和Amy是他的直接下属。

然而，我想编写一个查询，显示所有属于Bob的人，因此它需要查看Bob的直接下属，以及他们的直接下属，依此类推——在这种情况下，结果将给出Margaret、Amy、Emma和Carol。

我假设这需要某种递归，但我完全卡住了...

- Mat Richardson

Answer 1

您应该使用递归CTE：

WITH RCTE AS 
(
    SELECT * FROM dbo.Supervision WHERE UserID = 1
    UNION ALL
    SELECT s.* FROM dbo.Supervision s 
        INNER JOIN RCTE r ON s.userID = r.superID
)
SELECT DISTINCT u.userID, u.userName 
FROM RCTE r
LEFT JOIN dbo.Users u ON r.superID = u.userID

SQLFiddle DEMO

- Nenad Zivkovic

---

这是否考虑了下面的管理关系，这似乎只返回了4个结果？ - JsonStatham

@SelectDistinct 当然可以。在这个例子中只有4个结果。尝试为处于层次结构顶部的用户6运行它，你将得到他下面的所有9个其他用户。http://sqlfiddle.com/#!6/c100f/2 - Nenad Zivkovic

啊，我明白了，我的答案也是这样做的吗？我根据原帖中的示例创建了表格，结果大约在20左右，这就是为什么我感到困惑的原因。 - JsonStatham

@SelectDistinct 是的，我已经检查了你的答案。没问题 - 你只是在CTE的第一部分缺少了“WHERE UserID = 1”条件 - 所以你得到的是所有可能的用户和主管的组合，而不仅仅是一个给定用户下面的人。- http://sqlfiddle.com/#!6/745a8/4 - Nenad Zivkovic

对不起，我误读了问题，我以为所有的组合都是必需的！ - JsonStatham

Answer 2

WITH MyCTE
AS ( 

-- ID's and Names
SELECT SuperID, ID
FROM Users
join dbo.Supervision
on ID = dbo.Supervision.UserID
WHERE UserID = 1

UNION ALL

--Who Manages who...
SELECT s.SuperID, ID
FROM Supervision s
INNER JOIN MyCTE ON s.UserID = MyCTE.SuperID
WHERE s.UserID IS NOT NULL 

)

SELECT distinct MyCTE.ID, NAMES.UserName, '<------Reports to ' as Hierarchy, res_name.UserName
FROM MyCTE
join dbo.Users NAMES on 
MyCTE.ID = NAMES.ID 

join dbo.Users res_name 
on res_name.ID = MyCTE.SuperID

order by MyCTE.ID, NAMES.UserName, res_name.UserName

- JsonStatham

Answer 3

听起来你需要一个递归CTE。本文是一个入门指南，包括一个与你的例子相似的示例：

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

希望这有所帮助。

- Sam Meadley