MySQL查询指定日期区间数据：缺失日期补0并计算工时

我来帮你搞定这个问题！你需要的是生成连续日期序列 + 左连接业务表 + 处理空值计算工时的组合方案，我给你拆解一下具体怎么做：

核心思路 #

因为你需要保留区间内的所有日期（哪怕当天没有数据），所以必须用**左连接（LEFT JOIN）**让日期序列作为主表，业务表作为从表，这样所有日期都会被保留。再用COALESCE函数把空值转换成0，确保无数据日期的工时计算结果为0。

具体SQL示例 #

情况1：业务表中每天最多一条记录 #

假设你的日期序列生成SQL是用CTE（Common Table Expression）实现的，业务表名为time_records，日期字段为record_date，工时字段为regular、deduct、overtime：

-- 先生成2018-01-01至2018-01-09的连续日期序列
WITH date_range AS (
    -- PostgreSQL版本的生成方式
    SELECT generate_series('2018-01-01'::DATE, '2018-01-09'::DATE, '1 day'::INTERVAL) AS work_date
    -- 如果是MySQL 8.0+，用递归CTE：
    -- WITH RECURSIVE date_range AS (
    --     SELECT '2018-01-01' AS work_date
    --     UNION ALL
    --     SELECT DATE_ADD(work_date, INTERVAL 1 DAY) FROM date_range WHERE work_date < '2018-01-09'
    -- )
)
-- 左连接业务表并计算工时
SELECT
    dr.work_date,
    -- 用COALESCE把null值转成0，确保无数据时Hours为0
    COALESCE(tr.regular, 0) - COALESCE(tr.deduct, 0) + COALESCE(tr.overtime, 0) AS Hours
FROM date_range dr
-- 左连接保证所有日期都被保留
LEFT JOIN time_records tr ON dr.work_date = tr.record_date
-- 按日期排序
ORDER BY dr.work_date;

情况2：业务表中每天可能有多条记录 #

如果同一天有多个工时记录，需要先按日期聚合总和，再和日期序列连接：

WITH date_range AS (
    SELECT generate_series('2018-01-01'::DATE, '2018-01-09'::DATE, '1 day'::INTERVAL) AS work_date
),
-- 先聚合每天的工时总和
daily_agg AS (
    SELECT
        record_date,
        SUM(regular) AS total_regular,
        SUM(deduct) AS total_deduct,
        SUM(overtime) AS total_overtime
    FROM time_records
    WHERE record_date BETWEEN '2018-01-01' AND '2018-01-09'
    GROUP BY record_date
)
SELECT
    dr.work_date,
    COALESCE(da.total_regular, 0) - COALESCE(da.total_deduct, 0) + COALESCE(da.total_overtime, 0) AS Hours
FROM date_range dr
LEFT JOIN daily_agg da ON dr.work_date = da.record_date
ORDER BY dr.work_date;

关键注意点 #

1. 日期类型匹配：确保date_range里的work_date和业务表的record_date都是DATE类型。如果业务表日期带时间（比如DATETIME），需要用CAST(tr.record_date AS DATE)转换后再连接，避免匹配失败。
2. COALESCE的作用：如果某天没有业务数据，regular、deduct、overtime会是null，直接计算会得到null，用COALESCE(字段, 0)可以把null转成0，保证工时计算结果为0。
3. 日期序列验证：先单独运行date_range的CTE，确认它生成了2018-01-01到2018-01-09的所有日期，没有遗漏或多余。

如果还是没得到预期结果，可以检查一下连接条件是否正确，或者业务表的字段是否有特殊的null值情况哦。

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