MySQL：日期范围展开为单个日期及营业日期查询问题

嘿，这个动态计算重复日程的需求我之前做过不少，刚好能给你捋清楚思路！咱们先从核心逻辑入手，再针对两个查询场景给出具体的实现方案：

首先咱们得把你的规则表字段定义清楚（方便后续逻辑落地）：

• start_date：规则的起始日期（比如每周二的第一个周二、每月第三个周五的那个周五、圣诞节的12-25）
• repeat_interval：重复间隔天数（每周填7，每月简化填21，无重复填NULL）
• status：状态（'营业'/'歇业'）

这个场景的核心是判断目标日期是否匹配某条营业规则，分三种规则类型处理：

逻辑步骤 #

• 无重复规则：直接判断目标日期是否等于start_date，且状态为'营业'
• 每周重复规则：目标日期和start_date是同一个星期几，且目标日期≥start_date，同时两者的天数差是7的整数倍
• 每月重复规则（简化版）：目标日期和start_date是同一个星期几，且目标日期≥start_date，同时两者的天数差是21的整数倍（按你给出的示例简化处理）

伪代码示例（SQL） #

-- 替换@target_date为你要查询的日期
SELECT 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM business_rules
            WHERE 
                -- 匹配无重复的营业规则
                (repeat_interval IS NULL AND start_date = @target_date AND status = '营业')
                OR
                -- 匹配每周重复的营业规则
                (repeat_interval = 7 
                 AND DAYOFWEEK(start_date) = DAYOFWEEK(@target_date) 
                 AND DATEDIFF(@target_date, start_date) % 7 = 0 
                 AND @target_date >= start_date 
                 AND status = '营业')
                OR
                -- 匹配每月重复的营业规则（简化21天间隔）
                (repeat_interval = 21 
                 AND DAYOFWEEK(start_date) = DAYOFWEEK(@target_date) 
                 AND DATEDIFF(@target_date, start_date) % 21 = 0 
                 AND @target_date >= start_date 
                 AND status = '营业')
        ) THEN '是' ELSE '否' END AS is_open;

这个场景需要对每条营业规则，计算其在区间内的有效次数，最后求和，同样分规则类型处理：

逻辑步骤 #

1. 无重复规则：如果start_date在区间[start_range, end_range]内且状态为'营业'，计数1，否则0
2. 每周/每月重复规则：
   • 先找到区间内第一个符合规则的日期（如果起始日期早于区间开始，就往后推到第一个进入区间的匹配日期）
   • 再找到区间内最后一个符合规则的日期
   • 如果第一个匹配日期晚于区间结束，次数为0；否则次数 = ((最后日期 - 第一个日期)/间隔天数) + 1

伪代码示例（SQL） #

-- 替换@start_range和@end_range为你的查询区间
WITH rule_counts AS (
    -- 计算无重复规则的次数
    SELECT
        CASE 
            WHEN repeat_interval IS NULL 
                 AND start_date BETWEEN @start_range AND @end_range 
                 AND status = '营业' THEN 1
            ELSE 0
        END AS count
    FROM business_rules

    UNION ALL

    -- 计算每周重复规则的次数
    SELECT
        CASE
            WHEN DAYOFWEEK(start_date) = DAYOFWEEK(first_match) THEN
                IF(first_match > @end_range, 0, FLOOR(DATEDIFF(@end_range, first_match)/7) + 1)
            ELSE 0
        END AS count
    FROM business_rules
    CROSS JOIN (
        -- 生成区间内第一个匹配的日期
        SELECT 
            CASE 
                WHEN start_date >= @start_range THEN start_date
                ELSE DATE_ADD(start_date, INTERVAL CEIL(DATEDIFF(@start_range, start_date)/7) * 7 DAY)
            END AS first_match
    ) AS first_dates
    WHERE repeat_interval = 7 AND status = '营业'

    UNION ALL

    -- 计算每月重复规则（简化21天间隔）的次数
    SELECT
        CASE
            WHEN DAYOFWEEK(start_date) = DAYOFWEEK(first_match) THEN
                IF(first_match > @end_range, 0, FLOOR(DATEDIFF(@end_range, first_match)/21) + 1)
            ELSE 0
        END AS count
    FROM business_rules
    CROSS JOIN (
        SELECT 
            CASE 
                WHEN start_date >= @start_range THEN start_date
                ELSE DATE_ADD(start_date, INTERVAL CEIL(DATEDIFF(@start_range, start_date)/21) * 21 DAY)
            END AS first_match
    ) AS first_dates
    WHERE repeat_interval = 21 AND status = '营业'
)
-- 汇总所有规则的营业次数
SELECT SUM(count) AS total_business_days FROM rule_counts;

• 如果你的每月重复规则需要更精准（比如严格匹配“当月第三个周五”，而不是固定21天间隔），可以调整逻辑：判断目标日期是当月的第几个周五，是否和start_date的顺位一致，同时月份差是整数倍。
• 要处理边界情况：比如起始日期晚于区间结束、区间完全不包含任何匹配日期等，避免出现负数或错误计数。

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