垃圾维度下的计数分组：员工假期状态统计SQL优化问询

API 探路者

2026-10-7

多假期状态统计最优SQL实现方案

核心采用条件聚合方案即可满足需求，无需使用UNION ALL拼接多段查询，也不需要复杂的多字段PIVOT语法，仅一次表扫描即可完成全量统计，性能和可维护性均远高于现有UNION ALL写法，可直接扩展到全部7类假期的统计需求。

实现代码示例

SELECT
  Period AS 统计年份,
  -- 病假三类特殊状态计数
  COUNT(CASE WHEN el.Sick_Leave_Desc = '缺失' THEN 1 END) AS 病假_缺失人数,
  COUNT(CASE WHEN el.Sick_Leave_Desc = '不适用' THEN 1 END) AS 病假_不适用人数,
  COUNT(CASE WHEN el.Sick_Leave_Desc = '已撤回' THEN 1 END) AS 病假_已撤回人数,
  -- 娱乐休假三类特殊状态计数
  COUNT(CASE WHEN el.Recreation_Leave_Desc = '缺失' THEN 1 END) AS 娱乐休假_缺失人数,
  COUNT(CASE WHEN el.Recreation_Leave_Desc = '不适用' THEN 1 END) AS 娱乐休假_不适用人数,
  COUNT(CASE WHEN el.Recreation_Leave_Desc = '已撤回' THEN 1 END) AS 娱乐休假_已撤回人数,
  -- 看护假三类特殊状态计数
  COUNT(CASE WHEN el.Carers_Leave_Desc = '缺失' THEN 1 END) AS 看护假_缺失人数,
  COUNT(CASE WHEN el.Carers_Leave_Desc = '不适用' THEN 1 END) AS 看护假_不适用人数,
  COUNT(CASE WHEN el.Carers_Leave_Desc = '已撤回' THEN 1 END) AS 看护假_已撤回人数
FROM
  Employee_Fact ef
INNER JOIN
  Employee_Leave el ON ef.Employee_Leave_SK = el.Employee_Leave_PK
-- 可自定义WHERE条件过滤指定统计范围，如 WHERE Period BETWEEN 2020 AND 2024
GROUP BY
  Period
ORDER BY
  Period DESC;