如何在SQL中实现指定员工当年分月休假日期及类型统计

核心思路 #

要达成需求，需完成三个关键环节：将休假时间段拆分为单日记录、添加精准筛选条件、关联所需业务数据表，最终输出每个日期对应的休假类型。

具体实现步骤与SQL示例 #

以下是基于常见场景的完整实现方案，你可根据实际数据表结构调整：

1. 生成当年全量日期序列（若已有此逻辑可跳过）

用递归CTE生成当年1月1日至12月31日的所有日期，确保覆盖全年统计范围：

WITH YearFullDates AS (
    SELECT DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM YearFullDates
    WHERE stat_date < DATE_FORMAT(CURRENT_DATE(), '%Y-12-31')
)

2. 关联休假表与其他数据表并添加筛选

假设需关联员工表_Employees获取员工基础信息，完整SQL如下：

WITH YearFullDates AS (
    SELECT DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM YearFullDates
    WHERE stat_date < DATE_FORMAT(CURRENT_DATE(), '%Y-12-31')
)
SELECT
    yfd.stat_date,
    ld.LeaveType,
    e.emp_name -- 从关联表获取的额外字段，可替换为实际字段名
FROM YearFullDates yfd
-- 关联休假详情表，匹配日期落在休假时间段内的记录
LEFT JOIN _LeaveDetails ld
    ON yfd.stat_date BETWEEN ld.fromdate AND ld.toDate
    AND ld.empId = 'EMP001' -- 替换为目标员工ID
-- 关联其他业务表，示例为员工表
LEFT JOIN _Employees e
    ON ld.empId = e.emp_id
-- 强制筛选当年数据，避免跨年份干扰
WHERE YEAR(yfd.stat_date) = YEAR(CURRENT_DATE())
-- 按日期排序，保证结果有序
ORDER BY yfd.stat_date;

关键细节说明 #

• 筛选条件调整：
  • 若需指定固定年份统计，将YEAR(CURRENT_DATE())替换为具体年份（如2024）；
  • 若只需展示有休假的日期，将LEFT JOIN改为INNER JOIN即可。
• 多表关联扩展：
  若需关联休假类型字典表（如_LeaveTypeDict）展示休假类型名称，可添加额外关联：

  LEFT JOIN _LeaveTypeDict ltd
      ON ld.LeaveType = ltd.type_code
  

• 直接拆分休假时间段：
  若无需保留全年所有日期，仅需拆分员工当年的休假时间段为单日记录，可使用以下递归CTE：

  WITH SplitLeaveDays AS (
      SELECT
          empId,
          LeaveType,
          fromdate AS stat_date,
          toDate
      FROM _LeaveDetails
      WHERE empId = 'EMP001' AND YEAR(fromdate) = YEAR(CURRENT_DATE())
      UNION ALL
      SELECT
          empId,
          LeaveType,
          DATE_ADD(stat_date, INTERVAL 1 DAY),
          toDate
      FROM SplitLeaveDays
      WHERE stat_date < toDate
  )
  SELECT
      sld.stat_date,
      sld.LeaveType,
      e.emp_name
  FROM SplitLeaveDays sld
  LEFT JOIN _Employees e ON sld.empId = e.emp_id
  ORDER BY sld.stat_date;
  

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