MySQL 5中基于日期区间关联两表并更新数据的问题

错误原因分析 #

你的关联查询存在两个核心问题：

1. 分组维度不足：仅按user分组会将该用户所有封禁期间的被拒绝访问记录合并统计，无法区分不同封禁时间段的独立数据。
2. 非聚合字段未加入分组：emr.date1和emr.date2未包含在GROUP BY中，MySQL宽松SQL模式下会随机选取一条记录的日期返回，导致时间段与统计值不匹配。

正确的统计查询语句 #

要准确统计每个封禁时间段内的被拒绝访问次数，需按user、date1、date2共同分组：

SELECT
    emr.date1,
    emr.date2,
    emr.user,
    COUNT(*) AS access_denied
FROM
    dotable1 asr
JOIN dotable2 emr 
    ON asr.user = emr.user 
    AND asr.access_date BETWEEN emr.date1 AND emr.date2
WHERE
    asr.user = 'Will' 
    AND asr.blocked = 'Y'
GROUP BY
    emr.user, emr.date1, emr.date2;

执行后将得到每个封禁时间段对应的正确统计结果，其中2023-07-12至2023-07-28的access_denied值为613。

实现更新dotable2的语句 #

使用UPDATE JOIN将统计结果更新到dotable2的access_denied字段：

UPDATE dotable2 emr
JOIN (
    SELECT
        emr_inner.user,
        emr_inner.date1,
        emr_inner.date2,
        COUNT(*) AS access_count
    FROM
        dotable1 asr
    JOIN dotable2 emr_inner 
        ON asr.user = emr_inner.user 
        AND asr.access_date BETWEEN emr_inner.date1 AND emr_inner.date2
    WHERE
        asr.blocked = 'Y'
    GROUP BY
        emr_inner.user, emr_inner.date1, emr_inner.date2
) stats 
    ON emr.user = stats.user 
    AND emr.date1 = stats.date1 
    AND emr.date2 = stats.date2
SET emr.access_denied = stats.access_count;

如果只需要更新Will的特定时间段，可以在子查询的WHERE中添加asr.user = 'Will'来缩小范围。

验证更新结果 #

执行更新后，查询dotable2即可看到预期结果：

SELECT date1, date2, user, access_denied FROM dotable2 WHERE user = 'Will';

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