如何简化MySQL中按月批量更新表列的冗长SQL语句？

核心思路 #

把重复的逐天更新语句替换成单条UPDATE JOIN语句，先预计算目标月份所有日期的expected_avg值，再关联原表一次性完成更新，同时通过变量适配任意月份。

简化后的更新语句 #

-- 定义目标月份（可替换为任意月份，格式'YYYY-MM-01'）
SET @target_month = '2022-09-01';

-- 一次性更新目标月份所有日期的expected_avg字段
UPDATE `table` t
JOIN (
    SELECT
        dt.date_received,
        ROUND(COUNT(t2.req_id) / 6) AS expected_avg
    FROM (
        -- 生成目标月份的所有日期
        SELECT DATE_ADD(@target_month, INTERVAL (seq) DAY) AS date_received
        FROM (
            SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
            UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
            UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
            UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27
            UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
        ) seq
        WHERE DATE_ADD(@target_month, INTERVAL (seq) DAY) < DATE_ADD(@target_month, INTERVAL 1 MONTH)
    ) dt
    LEFT JOIN `table2` t2 ON 
        t2.date_received IS NOT NULL
        AND t2.date_received >= DATE_SUB(dt.date_received, INTERVAL 6 WEEK)
        AND t2.date_received <= dt.date_received
        AND DAYOFWEEK(t2.date_received) = DAYOFWEEK(dt.date_received)
        AND t2.dept_name = 'aClientName'
    GROUP BY dt.date_received
) calc ON t.date_received = calc.date_received
SET t.expected_avg = calc.expected_avg
WHERE t.date_received >= @target_month 
  AND t.date_received < DATE_ADD(@target_month, INTERVAL 1 MONTH);

进一步优化：合并创建与计算流程 #

如果允许调整原流程，还可以直接在创建table时就计算出expected_avg，省去后续的ALTER和UPDATE步骤：

-- 定义目标月份
SET @target_month = '2022-09-01';

CREATE TABLE `table` AS
SELECT
    DATE_FORMAT(t2.date_received, '%Y/%m/%d') AS date_received,
    COUNT(t2.req_id) AS received_total,
    ROUND(COUNT(t3.req_id) / 6) AS expected_avg
FROM `table2` t2
LEFT JOIN `table2` t3 ON
    t3.date_received IS NOT NULL
    AND t3.date_received >= DATE_SUB(t2.date_received, INTERVAL 6 WEEK)
    AND t3.date_received <= t2.date_received
    AND DAYOFWEEK(t3.date_received) = DAYOFWEEK(t2.date_received)
    AND t3.dept_name = 'aClientName'
WHERE t2.date_received IS NOT NULL
  AND t2.date_received >= @target_month
  AND t2.date_received < DATE_ADD(@target_month, INTERVAL 1 MONTH)
  AND t2.dept_name = 'aClientName'
GROUP BY DATE_FORMAT(t2.date_received, '%Y/%m/%d')
ORDER BY date_received DESC;

说明 #

• 替换@target_month的值即可适配任意月份，比如SET @target_month = CURDATE() - INTERVAL (DAY(CURDATE())-1) DAY;可自动适配当前月份。
• 日期生成部分用UNION生成0-30的序列，覆盖所有月份的最多天数（31天的月份也会自动过滤超出部分）。
• 用JOIN方式替代逐行子查询，大幅提升执行效率，尤其当数据量大时。

内容的提问来源于stack exchange，提问作者rob-roibu