MySQL如何关联员工表统计各岗位在职人数与空缺数量

实现方案 #

直接以岗位表为主表左连接员工表做分组统计即可，注意必须用左连接，否则没有在职员工的岗位会被过滤，无法统计到0在职的岗位数据。

SELECT
  jp.job_description AS job_position,
  COUNT(e.emp_id) AS no_of_active_emp,
  GREATEST(jp.job_quantity - COUNT(e.emp_id), 0) AS no_of_vacancies
FROM tbl_job_position jp
LEFT JOIN tbl_employee e
  ON jp.job_id = e.emp_pos_id
GROUP BY jp.job_id, jp.job_description, jp.job_quantity;

逻辑说明 #

• 在职人数统计用COUNT(e.emp_id)：左连接时，没有匹配员工的岗位对应的e.emp_id为NULL，COUNT会自动忽略NULL值，刚好得到正确的在职人数，不会把空值误算为1
• 空缺数计算用GREATEST(编制数-在职人数, 0)：避免出现在职人数超过编制数时返回负数空缺的情况，符合常规业务统计逻辑
• 分组时带上主键job_id：兼容MySQL 5.7+开启ONLY_FULL_GROUP_BY的运行模式，也能避免岗位重名导致的统计错误

执行上述语句后得到的结果和给出的预期报表完全一致。

内容的提问来源于stack exchange，提问作者Ainz