如何在 SQL 代码中遍历树/使用分层数据 [英] How To Traverse a Tree/Work With Hierarchical data in SQL Code
问题描述
假设我有一个员工表，其中有我公司每位员工的记录，以及主管的一列(如下所示).我想准备一份报告，其中列出了监督线中每个步骤的名称和标题.例如，对于 dick robbins，1d #15，我想要他的指挥链"中每个主管的列表，一直到总统，大奶酪.我想避免使用游标，但如果这是唯一的方法，那也没关系.
Say I have an employee table, with a record for each employee in my company, and a column for supervisor (as seen below). I would like to prepare a report, which lists the names and title for each step in a supervision line. eg for dick robbins, 1d #15, i'd like a list of each supervisor in his "chain of command," all the way to the president, big cheese. I'd like to avoid using cursors, but if that's the only way to do this then that's ok.
id fname lname title supervisorid
1 big cheese president 1
2 jim william vice president 1
3 sally carr vice president 1
4 ryan allan senior manager 2
5 mike miller manager 4
6 bill bryan manager 4
7 cathy maddy foreman 5
8 sean johnson senior mechanic 7
9 andrew koll senior mechanic 7
10 sarah ryans mechanic 8
11 dana bond mechanic 9
12 chris mcall technician 10
13 hannah ryans technician 10
14 matthew miller technician 11
15 dick robbins technician 11
真实数据的深度可能不会超过 10 级……但我宁愿不只是执行 10 个外部联接……我希望有比这更好的东西，而且比游标涉及的更少.
The real data probably won't be more than 10 levels deep...but I'd rather not just do 10 outside joins...I was hoping there was something better than that, and less involved than cursors.
感谢您的帮助.
推荐答案
这基本上是我在 OP 评论中链接到的关于我的问题的已接受答案的一个端口.
This is basically a port of the accepted answer on my question that I linked to in the OP comments.
您可以使用公用表表达式
you can use common-table expressions
WITH Family As
(
SELECT e.id, e.supervisorid, 0 as Depth
FROM Employee e
WHERE id = @SupervisorID
UNION All
SELECT e2.ID, e2.supervisorid, Depth + 1
FROM Employee e2
JOIN Family
On Family.id = e2.supervisorid
)
SELECT*
FROM Family
更多:
这篇关于如何在 SQL 代码中遍历树/使用分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
