如何在MySQL中基于指定日期自动生成往前回溯的周日期格式化结果？

刚好碰到过类似的需求，这就给你两种适配不同MySQL版本的方案，完美实现只输入一个日期就自动回溯生成对应周/年格式的结果！

一、MySQL 8.0+版本（推荐，支持递归CTE） #

MySQL 8.0及以上支持递归公共表表达式（CTE），可以轻松生成需要的周偏移量，不用手动写每个日期：

1. 生成多行结果（每个VAR一行） #

-- 替换这里的输入日期即可
SET @input_date = '2018-01-29';

WITH RECURSIVE week_offsets AS (
    -- 初始行：偏移量0（当前周）
    SELECT 0 AS offset_num
    UNION ALL
    -- 递归生成偏移量1到5（对应回溯1到5周）
    SELECT offset_num + 1 FROM week_offsets WHERE offset_num < 5
)
SELECT 
    -- 计算对应日期并格式化，别名对应VAR_5到VAR_0
    DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') AS CONCAT('VAR_', 5 - offset_num)
FROM week_offsets
ORDER BY offset_num;

2. 生成一行多列结果（和原手动查询格式一致） #

如果需要和你原来的手动查询一样，一行显示所有VAR_5到VAR_0，可以用条件聚合转换：

SET @input_date = '2018-01-29';

WITH RECURSIVE week_offsets AS (
    SELECT 0 AS offset_num
    UNION ALL
    SELECT offset_num + 1 FROM week_offsets WHERE offset_num < 5
)
SELECT 
    MAX(CASE WHEN offset_num = 0 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_5,
    MAX(CASE WHEN offset_num = 1 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_4,
    MAX(CASE WHEN offset_num = 2 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_3,
    MAX(CASE WHEN offset_num = 3 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_2,
    MAX(CASE WHEN offset_num = 4 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_1,
    MAX(CASE WHEN offset_num = 5 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_0
FROM week_offsets;

二、MySQL 5.x版本（兼容旧版，无递归CTE） #

如果你的MySQL版本低于8.0，没法用递归CTE，可以手动生成偏移量集合：

SET @input_date = '2018-01-29';

SELECT 
    MAX(CASE WHEN offset_num = 0 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_5,
    MAX(CASE WHEN offset_num = 1 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_4,
    MAX(CASE WHEN offset_num = 2 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_3,
    MAX(CASE WHEN offset_num = 3 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_2,
    MAX(CASE WHEN offset_num = 4 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_1,
    MAX(CASE WHEN offset_num = 5 THEN DATE_FORMAT(DATE_SUB(@input_date, INTERVAL offset_num * 7 DAY), '%v/%Y') END) AS VAR_0
FROM (
    -- 手动生成0到5的偏移量
    SELECT 0 AS offset_num UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
) AS week_offsets;

注意事项 #

• %v是ISO周数，以周一为一周的起始；如果需要以周日为起始的周数，替换成%U即可。
• 如果你需要回溯更多/更少周，只需要调整递归终止条件（比如< 5改成< 3就是回溯3周，共4个VAR）或者手动偏移量的数量。

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