获得直接或间接向雇员报告的所有员工,层级号码 [英] Get all employee who directly or indirectly reports to an employee, with hierarchy level no
问题描述
我有一个Employee表,如
pre $ $ $ c $ emp_id bigint $ b $ report_to bigint $ b $ emp_name varchar 20),
约束[PK_Emp]主键(emp_id),
约束[FK_Emp]外键(reports_to)引用[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)报告的雇员,以及层级如下:
emp_id hierarchy_level emp_name
------ --------------- ----------
2 1 Arpita
4 1 Sujon
5 2 Arpan
6 3 Jayanti
我是SQL新手,找出要使用什么或如何获得这些结果。值得使用表值变量的存储过程,或者只是一个Tsql select查询就足够了。
所有我做的是 - >选择Ep.emp_id,ep.emp_eame
从Emp作为E
Inner加入Emp作为Ep on Ep.reports_to = E.Emp_id
其中E.reports_to = 1或E.emp_id = 1 ;
但是这是准确的2级,我甚至不能生成hierarchy_level no。
任何建议,想法............将是最有帮助的.........
; CTE为
(
选择emp_id
,reports_to
,emp_name
,1作为等级
从Emp
where emp_name ='Sumanta'
union all
从emp child
中选择child.emp_id
,child.reports_to
,child.emp_name
,level + 1
CTE父母
on child.reports_to = parent.emp_id
)
select *
from CTE
SQL小提琴示例 p>
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:
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.
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.........
You could use a recursive 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
这篇关于获得直接或间接向雇员报告的所有员工,层级号码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!