如何显示各部门各岗位的员工人数

Question

我有两个表，分别名为Employee和Department。

员工(Emp_ID、姓名、职位、DeptID)

示例输出：

Dept_Name         tech_support       data_entry        assistant manager
Accounts               5                 6                 2
Production             2                 4                 2
Sales/Marketing        0                 6                 3

Answer 1

使用Department到Employee的左联接和条件聚合：

select
  d.dept_name,
  sum(case when e.position = 'tech_support' then 1 else 0 end) tech_support,
  sum(case when e.position = 'data_entry' then 1 else 0 end) data_entry,
  sum(case when e.position = 'assistant_manager' then 1 else 0 end) assistant_manager
from department d left join employee e
on e.deptid = d.department_id
group by d.department_id, d.dept_name