sql - 是否可以在单个查询中获得每个部门的员工总数和员工总数？

Question

我有一个名为 employee 的表，如下所示:

emp      dept
----     ----
1         HR
2         Accounts
3         HR
4         Dev
2         Dev

一名员工可能属于多个部门，例如员工 2。

我想要这样的输出:

dept    empInDept   totalCountofEmp
 -----    ---------   ----------------
    HR       2            4
    Accounts 1            4

我可以统计指定部门的员 worker 数，如下所示:

select dept,count(*) as empInDept from employees where dept ='HR' or dept='Accounts' group by dept

但我不确定是否可以创建一个单个查询，它可以获得上述select查询的结果，并且还可以获得此表中的员工。

Answer 1

这是您的请求示例:

create table #temp
(id int, value int)

insert into #temp values (1,2),(2,3),(1,5)

select id, count(Value), (select count(distinct value) from #temp) as X
from #temp
group by id

对于您的特定任务，请尝试以下操作:

select dept,
       count(*) as empInDept 
       (select count(distinct emp) from employees) as TotalCount
from employees where dept ='HR' or dept='Accounts' 
group by dept