如何在MariaDB查询中根据年、月、周获取周首日的DAYOFMONTH？

针对你需要的**周一为周起始日、首周最小天数为1（对应MariaDB WEEK函数mode7）**的规则，我们可以通过构建SQL函数或查询语句，直接根据年份、月份和周数获取对应周首日的DAYOFMONTH值，同时确保该周与目标月份存在重叠（完全匹配你给出的示例逻辑）。

核心思路 #

1. 定位目标年份第N周的首日（周一）：基于mode7的周规则，先找到年份第一天所在的周一（即第1周的首日），再通过周数偏移得到目标周的首日。
2. 验证周与月份的重叠性：确保该周的日期范围至少有一天落在目标月份内，避免无效的周数查询。
3. 返回首日的日期天数：若周与月份重叠，提取首日的DAYOFMONTH值；否则返回NULL（可按需调整）。

实现可复用函数 #

你可以创建一个自定义函数来完成这个计算，方便后续多次调用：

DELIMITER //
CREATE FUNCTION get_week_first_day_dayofmonth(
    p_year INT,
    p_month INT,
    p_week INT
) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE v_year_first DATE;
    DECLARE v_first_day_of_week DATE;
    DECLARE v_month_start DATE;
    DECLARE v_month_end DATE;
    DECLARE v_week_end DATE;

    -- 获取目标年份的第一天
    SET v_year_first = MAKEDATE(p_year, 1);
    
    -- 计算目标年份第p_week周的首日（周一，符合mode7规则）
    SET v_first_day_of_week = v_year_first 
        - INTERVAL ((DAYOFWEEK(v_year_first) + 5) % 7) DAY 
        + INTERVAL (p_week - 1) * 7 DAY;
    
    -- 获取目标月份的起止日期
    SET v_month_start = MAKEDATE(p_year, 1) + INTERVAL (p_month - 1) MONTH;
    SET v_month_end = LAST_DAY(v_month_start);
    
    -- 计算该周的最后一天（周日）
    SET v_week_end = v_first_day_of_week + INTERVAL 6 DAY;

    -- 验证周与月份是否重叠，符合条件则返回首日的天数
    IF v_first_day_of_week <= v_month_end AND v_week_end >= v_month_start THEN
        RETURN DAYOFMONTH(v_first_day_of_week);
    ELSE
        RETURN NULL; -- 无重叠时返回NULL，可根据需求改为报错或其他默认值
    END IF;
END //
DELIMITER ;

测试示例验证 #

用你提供的示例测试函数，结果完全匹配：

• SELECT get_week_first_day_dayofmonth(2020, 1, 1); → 返回30
• SELECT get_week_first_day_dayofmonth(2020, 1, 2); → 返回6
• SELECT get_week_first_day_dayofmonth(2020, 2, 5); → 返回27
• SELECT get_week_first_day_dayofmonth(2020, 6, 23); → 返回1
• SELECT get_week_first_day_dayofmonth(2020, 12, 53); → 返回28

逻辑细节说明 #

• 周首日计算：通过DAYOFWEEK函数转换年份第一天的星期值，调整到对应的周一（mode7的周起始），再通过(p_week-1)*7的偏移量得到目标周的首日。
• 重叠验证：确保目标周的结束日不早于月份第一天，且周的首日不晚于月份最后一天，这样跨月的周会同时被前后月份的对应周数查询命中（比如2020年第5周同时属于1月和2月）。

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