基于ISOWEEK与自定义财年的日期计算SQL查询修正求助

原SQL的问题在于使用固定减去52周模拟“回退一年”，但ISO周体系中一年可能包含53周，且闰年会导致日历年份差366天，52周（364天）的偏移无法精确对应上一年的同一周，因此部分日期计算错误。

以下是结合ISO周规则和财年逻辑修正后的SQL（针对MySQL环境）：

WITH cte AS (
    SELECT DATE'2025-05-08' AS input_dt -- 原错误案例
    UNION ALL
    SELECT DATE'2025-06-30' AS input_dt -- 原错误案例
    UNION ALL
    SELECT DATE'2025-08-15' AS input_dt -- 原正常案例
    UNION ALL
    SELECT DATE'2025-07-15' AS input_dt -- 原正常案例
),
week_calculations AS (
    SELECT 
        input_dt,
        -- 获取输入日期的ISO周（周一为周起始），格式为YYYYWW
        YEARWEEK(input_dt, 1) AS current_iso_yearweek,
        -- 计算输入日期所属ISO周的上一周：处理跨年周边界（当年第1周的上一周为上一年最后一周）
        CASE 
            WHEN RIGHT(YEARWEEK(input_dt, 1), 2) = '01'
            THEN CONCAT(YEAR(input_dt) - 1, LPAD(WEEKOFYEAR(STR_TO_DATE(CONCAT(YEAR(input_dt)-1, '-12-31'), '%Y-%m-%d'), 1), 2, '0'))
            ELSE YEARWEEK(input_dt, 1) - 1
        END AS prev_iso_yearweek
    FROM cte
)
SELECT 
    input_dt,
    -- 生成上一年对应周的周一日期
    STR_TO_DATE(CONCAT(LEFT(prev_iso_yearweek, 4)-1, RIGHT(prev_iso_yearweek, 2), ' Monday'), '%X%V %W') AS StartDt,
    -- 生成上一年对应周的周日日期（周一加6天）
    DATE_ADD(STR_TO_DATE(CONCAT(LEFT(prev_iso_yearweek, 4)-1, RIGHT(prev_iso_yearweek, 2), ' Monday'), '%X%V %W'), INTERVAL 6 DAY) AS EndDt
FROM week_calculations;

关键逻辑说明： #

1. ISO周定位：使用YEARWEEK(input_dt, 1)获取输入日期的ISO周（参数1指定周一为周起始），返回格式为YYYYWW，确保周的定义符合要求。
2. 上一周计算：处理跨年边界情况——如果当前周是当年第1周，上一周则为上一年的最后一周，避免周数计算错误。
3. 回退一年的周起止：通过STR_TO_DATE直接根据上一年的周数生成周一日期，再推导周日，确保结果始终是周一到周日的完整周，不受闰年或日历偏移影响。

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