SQL查询以获取每个经理下员工的递归计数 [英] SQL Query to get recursive count of employees under each manager

问题描述

我正在寻找一个 SQL 查询来获取每个经理或其他表中的某些经理下 Employee 的递归计数.

I'm looking for a SQL query to get the recursive count of Employee under the each manager or some managers from other table.

表结构是:

Emp_Table

Emp_id Manager_id
1         1
2         1
3         1
4         3
5         3
6         5
7         5
8         7
9         7
10        7

执行表:

此表不过是选定的经理在此处可用.我需要得到这个经理手下每个员工的数量

This table is nothing but selected managers are available here. I need to get the count of each employee under this manager

Manager_id
     1
     4
     7
     8

我正在寻找以下输出

Manager_ID   Count_of_Employees
     1             9
     4             0
     7             3 
     8             0

请注意，您的回答仅提供了我得到的每位经理的直接下属员工人数.Bout 我正在寻找与经理的递归员工报告.在我上面的示例中，根据您的查询，我将获得 Manager_id 1 计数为 3，但我正在寻找输出为 9.提前致谢！

Please note your answers gives only the direct report employee count for each manager only I'm getting. Bout I'm looking for recursive employee reports with managers. In my above example as per your query I will get for the Manager_id 1 count is 3 but I'm looking for the output is 9. Thanks in advance!

谢谢大家的回复.我终于得到了查询.请找出我的答案.

Thanks for all your reply. I finally got the Query. Please find my answer.

推荐答案

首先，重要说明:Emp_Table的第一行，其中Emp_id==Manager_Id==1 不仅没有意义，还会造成无限递归.我建议你删除它.

First off, an important note: The first row of the Emp_Table, where Emp_id==Manager_Id==1 is not only meaningless but will also cause infinite recursion. I suggest you remove it.

然而，为了提供答案，我首先创建了一个消除此类无效条目的视图，并使用了 that 而不是 Emp_Table:

In order to provide an answer, however, I first created a view that eliminates such invalid entries, and used that instead of Emp_Table:

create view valid_mng as 
select Emp_Id,Manager_id from Emp_Table
where Emp_Id<>Manager_Id

所以它归结为以下，在递归 CTE 的帮助下:

So it boils down to the following, with a little help of a recursive CTE:

With cte as (
  select Emp_Id,Manager_id from valid_mng
  union all
  select c.Emp_Id,e.Manager_Id 
  from cte c join valid_mng e on (c.Manager_Id=e.Emp_Id)
  )

select m.Manager_Id,count(e.Emp_Id) as Count_of_Employees
from [Execute] m
left join cte e on (e.Manager_Id=m.Manager_Id)
group by m.Manager_Id

如果您最终删除了有问题的行，或者更好地将 Manager_Id=NULL 设置为 HABO 建议，只需忽略 valid_mng查看并将其替换为 Emp_Table 无处不在.

If you eventually remove the offending row(s), or better yet set Manager_Id=NULL as HABO suggested, just ignore the valid_mng view and replace it with Emp_Table everywhere.

另附注:Execute 是 MSSQL 中的保留字.避免在用户对象命名中使用保留字总是一个好习惯.

Also a side note: Execute is a reserved word in MSSQL. Avoiding the use of reserved words in user obect naming is always a good practice.

这篇关于SQL查询以获取每个经理下员工的递归计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！