统计所有直接或间接向经理汇报的下属 [英] Count all subordinates who directly or indirectly reports to managers

问题描述

我在完成一项任务时遇到问题.

I got a problem with one task.

我需要计算所有直接或间接向特定经理报告的下属(不同的)

I need to count all subordinates (distinct) who directly or indirectly reports to a specific manager

我有一个像这样的 Employee 表:

I have an Employee table like this:

EMPLOYEE_ID Int,
MANAGER_ID Int,
EMPLOYEE_NAME varchar(200)

示例:

            Alex(1)
    --------------------
    Jhon(2)          Kevin(3)
------------------------------
Mike(4) Amanda(5)  Tom(6) Jery(7)

我只能计算直接向经理汇报的员工:

I can count only employee who directly reports to a manager:

SELECT 
    MANAGER_ID
   ,COUNT(MANAGER_ID) as SubCount
FROM [dbo].[EMPLOYEE]
GROUP BY MANAGER_ID

但结果我有这样的事情:

But in result I have something like this:

Manager_ID | SubCount
----------------------
1          | 2
2          | 2
3          | 2
----------------------

相反:

Manager_ID | SubCount
----------------------
1          | 6
2          | 2
3          | 2
----------------------

如果您有任何建议或想法，我会很高兴.

I will be happy for any suggestions or idea how to do this.

推荐答案

declare @t table(EMPLOYEE_ID Int,
MANAGER_ID Int,
EMPLOYEE_NAME varchar(200))
insert @t values(1,null,'Alex'),(2,1,'Jhon'),(3,1,'Kevin'),
(4,2,'Mike'),(5,2,'Amanda'),(6,3,'Tom'),(7,3,'Jerry')

;with a as
(
select EMPLOYEE_ID boss,EMPLOYEE_ID from @t t
  where exists (select 1 from @t where t.EMPLOYEE_ID = MANAGER_ID)
union all
select a.boss, t.EMPLOYEE_ID
from @t t join a on t.MANAGER_ID = a.EMPLOYEE_ID
)
--subtracting 1 because it is also counting the manager
select boss, count(*)-1 SubCount from a group by boss
option (maxrecursion 20)

这篇关于统计所有直接或间接向经理汇报的下属的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！