跨月病假场景下的月度病假天数统计查询优化咨询

嗨，这个跨月病假统计的坑我可太熟了！很多人一开始都会犯只看结束日期在当月的错误，结果漏掉了那些跨月假期里属于目标月的天数。别担心，咱们只需要调整筛选逻辑，再精准计算重叠日期的天数就能搞定。

核心思路 #

问题的根源是原来的查询只匹配结束日期在目标月份内的记录，但实际上只要病假时间段和目标月份有任何重叠（不管是开始在月前、结束在月后，还是开始在月内、结束在月后），我们都需要统计重叠部分的天数。

具体步骤分为两步：

1. 明确目标月份的起始和结束日期（比如3月的2024-03-01到2024-03-31）
2. 筛选出所有与目标月份有重叠的病假记录，再计算每条记录在目标月内的实际天数

数据库示例代码 #

下面针对主流数据库给出具体的查询示例，假设你的病假表名为employee_sick_leave，包含字段employee_id（员工ID）、start_date（病假开始日期）、end_date（病假结束日期）。

PostgreSQL 版本 #

-- 先定义目标月份的起止日期（这里以2024年3月为例）
WITH target_month AS (
    SELECT 
        DATE_TRUNC('month', '2024-03-01'::DATE) AS month_start,
        DATE_TRUNC('month', '2024-03-01'::DATE) + INTERVAL '1 month - 1 day' AS month_end
)
SELECT
    employee_id,
    -- 计算每条记录在目标月内的病假天数：重叠结束日 - 重叠开始日 + 1（包含首尾两天）
    SUM(
        (LEAST(sl.end_date, tm.month_end::DATE) - GREATEST(sl.start_date, tm.month_start::DATE)) + 1
    ) AS total_sick_days_in_march
FROM employee_sick_leave sl
CROSS JOIN target_month tm
-- 筛选条件：病假时间段与目标月份有重叠
WHERE 
    sl.start_date <= tm.month_end::DATE 
    AND sl.end_date >= tm.month_start::DATE
GROUP BY employee_id;

MySQL 版本 #

-- 设置目标月份，这里以2024年3月为例
SET @target_month = '2024-03-01';
SET @month_start = DATE_FORMAT(@target_month, '%Y-%m-01');
SET @month_end = LAST_DAY(@target_month);

SELECT
    employee_id,
    SUM(
        DATEDIFF(LEAST(end_date, @month_end), GREATEST(start_date, @month_start)) + 1
    ) AS total_sick_days_in_march
FROM employee_sick_leave
WHERE 
    start_date <= @month_end 
    AND end_date >= @month_start
GROUP BY employee_id;

逻辑说明 #

这个方案能覆盖所有跨月场景：

• 对于2024-03-29到2024-04-03的病假，重叠区间是2024-03-29到2024-03-31，计算得3天，正好统计到3月的部分
• 对于2024-02-28到2024-03-05的病假，重叠区间是2024-03-01到2024-03-05，计算得5天
• 完全在当月内的病假（比如2024-03-15到2024-03-20），会正常计算6天

注意事项 #

• 确保你的start_date和end_date字段是DATE类型，如果是DATETIME，可以用日期截断函数（比如PostgreSQL的DATE()、MySQL的DATE()）去掉时间部分再计算
• 不同数据库的日期函数略有差异，比如SQL Server可以用DATEADD和DATEDIFF调整，Oracle用TRUNC和MONTHS_BETWEEN，核心逻辑是一致的

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