如何设置以周一为起始日的月度周范围？MySQL技术求助

你当前的查询是按自然月日期段划分周（从每月1号开始每7天算一周），但需求是周以周一至周日为周期，且2022年10月的第一周从10月3日（周一）开始，最后一周延续到11月6日。

调整后的查询语句 #

SELECT
    week_num AS week,
    CONCAT('Week ', week_num) AS weekname,
    week_start AS 'Week Start',
    week_end AS 'Week End'
FROM (
    SELECT
        @week_num := @week_num + 1 AS week_num,
        @current_start := DATE_ADD(@current_start, INTERVAL 7 DAY) AS week_start,
        DATE_ADD(@current_start, INTERVAL 6 DAY) AS week_end
    FROM (
        -- 初始化：找到2022年10月的第一个周一
        SELECT
            @current_start := CASE
                -- DAYOFWEEK返回1=周日，2=周一
                WHEN DAYOFWEEK(first_day) = 2 THEN first_day
                ELSE DATE_ADD(first_day, INTERVAL (2 - DAYOFWEEK(first_day) + 7) % 7 DAY)
            END,
            @week_num := 0,
            first_day,
            last_day
        FROM (
            SELECT
                DATE('2022-10-01') AS first_day,
                LAST_DAY(DATE('2022-10-01')) AS last_day
        ) init
    ) vars
    -- 生成足够的周数，覆盖到当月最后一天后的一周
    WHERE @current_start <= DATE_ADD(last_day, INTERVAL 7 DAY)
) weeks;

关键修改说明 #

• 定位第一个周一：通过DAYOFWEEK函数判断当月第一天的星期，计算偏移量得到当月第一个周一的日期（2022年10月1日是周六，偏移2天得到10月3日）。
• 迭代生成周范围：使用变量@current_start逐周递推，每次加7天得到下一周的周一，再加6天得到对应周日。
• 跨月周处理：直接保留周结束日期到周日，即使超过当月最后一天（比如10月31日是周一，周结束自动延伸到11月6日）。

关联attendance表（可选） #

如果需要确保生成的周范围在attendance表中有对应数据，可以添加EXISTS关联：

SELECT
    week_num AS week,
    CONCAT('Week ', week_num) AS weekname,
    week_start AS 'Week Start',
    week_end AS 'Week End'
FROM (
    SELECT
        @week_num := @week_num + 1 AS week_num,
        @current_start := DATE_ADD(@current_start, INTERVAL 7 DAY) AS week_start,
        DATE_ADD(@current_start, INTERVAL 6 DAY) AS week_end
    FROM (
        SELECT
            @current_start := CASE
                WHEN DAYOFWEEK(first_day) = 2 THEN first_day
                ELSE DATE_ADD(first_day, INTERVAL (2 - DAYOFWEEK(first_day) + 7) % 7 DAY)
            END,
            @week_num := 0,
            first_day,
            last_day
        FROM (
            SELECT
                DATE('2022-10-01') AS first_day,
                LAST_DAY(DATE('2022-10-01')) AS last_day
        ) init
    ) vars
    WHERE @current_start <= DATE_ADD(last_day, INTERVAL 7 DAY)
) weeks
WHERE EXISTS (
    SELECT 1 FROM attendance
    WHERE attendance.date BETWEEN weeks.week_start AND weeks.week_end
)
ORDER BY week_num;

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