SQL中如何基于起止日期按年月维度聚合员工工时？

这个问题确实很常见——当工时记录的起止日期跨月份时，直接按日期分组会把整个记录算到一个月里，而我们需要把它拆分到对应的月份中。下面我会一步步给你解决思路和具体的SQL代码。

核心思路 #

1. 拆分跨月记录：把每条跨月的工时记录拆分成它所覆盖的每个月的独立条目。比如2019-09-28到2019-10-01的记录，要拆成9月和10月两个条目。
2. 计算每个月的有效天数：对于拆分后的每个月条目，计算该记录在当月实际覆盖的天数（比如9月覆盖28、29、30号，共3天；10月覆盖1号，共1天）。
3. 分摊工时到对应月份：根据总工时和总天数算出单日工时，再乘以当月的有效天数，得到该记录在当月的贡献工时。
4. 分组统计：最后按employee_id和年月维度汇总工时。

具体SQL实现（以SQL Server为例） #

假设你的原始表名为employee_hours，日期格式是MM-DD-YYYY（需要先转成标准日期类型），可以用递归CTE来拆分记录：

WITH date_range AS (
    -- 基础数据：转换日期格式，计算每条记录的总天数
    SELECT 
        employee_id,
        CONVERT(DATE, start_date, 101) AS original_start,
        CONVERT(DATE, end_date, 101) AS original_end,
        hours,
        DATEDIFF(DAY, CONVERT(DATE, start_date, 101), CONVERT(DATE, end_date, 101)) + 1 AS total_days
    FROM employee_hours

    UNION ALL

    -- 递归生成每个月的条目
    SELECT 
        employee_id,
        DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(original_start), MONTH(original_start), 1)) AS original_start,
        original_end,
        hours,
        total_days
    FROM date_range
    WHERE DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(original_start), MONTH(original_start), 1)) <= original_end
),
monthly_hours AS (
    -- 计算每个月的有效天数和分摊的工时
    SELECT 
        employee_id,
        -- 生成YYYYMM格式的年月
        FORMAT(DATEFROMPARTS(YEAR(original_start), MONTH(original_start), 1), 'yyyyMM') AS Year_Month,
        -- 计算当月的实际起止日期
        MAX(original_start) AS month_start,
        MIN(original_end) AS month_end,
        hours,
        total_days,
        -- 当月有效天数
        DATEDIFF(DAY, MAX(original_start), MIN(original_end)) + 1 AS month_days,
        -- 分摊到当月的工时：(总工时/总天数)*当月有效天数
        ROUND((CAST(hours AS FLOAT)/total_days) * (DATEDIFF(DAY, MAX(original_start), MIN(original_end)) + 1), 0) AS monthly_hour
    FROM date_range
    GROUP BY 
        employee_id,
        FORMAT(DATEFROMPARTS(YEAR(original_start), MONTH(original_start), 1), 'yyyyMM'),
        hours,
        total_days
)
-- 最终按员工和年月汇总
SELECT 
    employee_id,
    Year_Month,
    SUM(monthly_hour) AS hours
FROM monthly_hours
GROUP BY employee_id, Year_Month
ORDER BY employee_id, Year_Month;

代码说明 #

1. date_range CTE：首先把原始的字符串日期转成标准日期类型，计算每条记录的总天数；然后通过递归，把跨月的记录拆分成每个月的条目，直到覆盖完整个日期范围。
2. monthly_hours CTE：计算每个拆分后条目的当月有效天数，再按单日工时分摊得到当月的工时（这里用ROUND取整，符合你示例中的整数结果）。
3. 最终查询：按员工和年月分组求和，得到你需要的统计结果。

如果是MySQL环境 #

MySQL的递归语法和日期函数略有不同，对应的代码可以调整为：

WITH RECURSIVE date_range AS (
    SELECT 
        employee_id,
        STR_TO_DATE(start_date, '%m-%d-%Y') AS original_start,
        STR_TO_DATE(end_date, '%m-%d-%Y') AS original_end,
        hours,
        DATEDIFF(STR_TO_DATE(end_date, '%m-%d-%Y'), STR_TO_DATE(start_date, '%m-%d-%Y')) + 1 AS total_days
    FROM employee_hours

    UNION ALL

    SELECT 
        employee_id,
        DATE_ADD(DATE_FORMAT(original_start, '%Y-%m-01'), INTERVAL 1 MONTH) AS original_start,
        original_end,
        hours,
        total_days
    FROM date_range
    WHERE DATE_ADD(DATE_FORMAT(original_start, '%Y-%m-01'), INTERVAL 1 MONTH) <= original_end
),
monthly_hours AS (
    SELECT 
        employee_id,
        DATE_FORMAT(original_start, '%Y%m') AS Year_Month,
        GREATEST(original_start, DATE_FORMAT(original_start, '%Y-%m-01')) AS month_start,
        LEAST(original_end, LAST_DAY(original_start)) AS month_end,
        hours,
        total_days,
        DATEDIFF(LEAST(original_end, LAST_DAY(original_start)), GREATEST(original_start, DATE_FORMAT(original_start, '%Y-%m-01'))) + 1 AS month_days,
        ROUND((hours / total_days) * (DATEDIFF(LEAST(original_end, LAST_DAY(original_start)), GREATEST(original_start, DATE_FORMAT(original_start, '%Y-%m-01'))) + 1), 0) AS monthly_hour
    FROM date_range
)
SELECT 
    employee_id,
    Year_Month,
    SUM(monthly_hour) AS hours
FROM monthly_hours
GROUP BY employee_id, Year_Month
ORDER BY employee_id, Year_Month;

这样就能完美处理你示例中的情况：把2019-09-28到2019-10-01的32小时，拆分成9月的24小时（3天×8）加上之前2019-09-01到2019-09-02的8小时，总共32小时；10月的8小时（1天×8），和你期望的结果完全一致。

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