让所有员工直接或间接地向具有层次结构级别的员工报告

问题描述

我有一张员工表，比如

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

我希望得到所有员工直接或间接向Sumanta或emp_id（1）报告，以及层次结构级别，如下所示：

I want to get all the employees that directly or indirectly reports to Sumanta or emp_id(1), and with hierarchy level, like this:

emp_id         hierarchy_level         emp_name
------         ---------------        ----------
2                    1                  Arpita
4                    1                  Sujon
5                    2                  Arpan
6                    3                 Jayanti

我是SQL新手而且不能''找到使用什么或如何获得这些结果。值得存储过程与表值变量，或只是一个Tsql选择查询就足够了。我们非常欢迎任何帮助。



我所做的全部是 -

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.

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;

但这是准确的2级，我甚至无法生成hierarchy_level号。

任何建议，想法............将是最有帮助的.........

but this is accurate upto 2 level and I cant even generate the hierarchy_level no.
Any suggestion, idea............ will be most helpfull.........

推荐答案

With CTE as (
Select emp_id,emp_Name,reports_to,1 as Level From emp Where reports_to=1
Union all
Select E.emp_id,E.emp_name,E.reports_to,Level+1
From emp as E Inner join CTE on E.reports_to=CTE.emp_id )
Select * from CTE;

Quote：

无论如何，谢谢，帮助确切地寻找什么

Any way thanks though, for helping about exactly what to look for

这篇关于让所有员工直接或间接地向具有层次结构级别的员工报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！