SQL 获取所有直接或间接向员工报告的员工，层级为 no
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16232426/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get all employee who directly or indirectly reports to an employee, with hierarchy level no
提问by Sukanta
I have a Employee table like
我有一个员工表
emp_id bigint,
reports_to bigint,
emp_name varchar(20),
Constraint [PK_Emp] Primary key (emp_id),
Constraint [FK_Emp] Foreign key (reports_to) references [MSS].[dbo].[Emp]([emp_id])
emp_id reports_to emp_name
------ ------ --------------
1 null Sumanta
2 1 Arpita
3 null Pradip
4 1 Sujon
5 2 Arpan
6 5 Jayanti
I want to get all the employees that directly or indirectly reports to Sumanta or emp_id(1), and with hierarchy level, like this:
我想获得所有直接或间接向 Sumanta 或 emp_id(1) 报告的员工，并具有层次结构，如下所示：
emp_id hierarchy_level emp_name
------ --------------- ----------
2 1 Arpita
4 1 Sujon
5 2 Arpan
6 3 Jayanti
I am new to SQL and just couldn't find what to use or how to get those results. Is it worth a stored procedure with table valued variable, or just a Tsql select query will be enough. Any help is most welcome.
我是 SQL 的新手，只是找不到要使用的内容或如何获得这些结果。是否值得一个带有表值变量的存储过程，或者只是一个 Tsql 选择查询就足够了。任何帮助都是最受欢迎的。
All I have done is-
我所做的就是——
Select Ep.emp_id,ep.emp_eame
From Emp as E
Inner Join Emp as Ep on Ep.reports_to=E.Emp_id
Where E.reports_to=1 or E.emp_id=1;
but this is accurate upto 2 level and I cant even generate the hierarchy_level no. Any suggestion, idea............ will be most helpfull.........
但这准确到 2 级，我什至无法生成hierarchy_level 编号。任何建议，想法…………将是最有帮助的…………
回答by Andomar
You could use a recursive CTE:
您可以使用递归 CTE：
; with CTE as
(
select emp_id
, reports_to
, emp_name
, 1 as level
from Emp
where emp_name = 'Sumanta'
union all
select child.emp_id
, child.reports_to
, child.emp_name
, level + 1
from Emp child
join CTE parent
on child.reports_to = parent.emp_id
)
select *
from CTE
