SQL如何从多行双日期记录中提取排除周末的连续时段起止日期

核心思路 #

采用间隙合并（Gaps and Islands）经典算法逻辑，通过分阶段标记连续块实现需求，全程不依赖日历表，仅通过DATEFIRST配置处理周末判断逻辑。 #

步骤1：统一周计算规则 #

设置DATEFIRST = 1将周一设为一周的第一天，此时周五对应的DATEPART(WEEKDAY, 日期)返回值为5，周六为6、周日为7，简化跨周末的判断逻辑：

SET DATEFIRST 1; 
-- 其他数据库可替换为对应语法，例如MySQL用 SET @@default_week_format = 1;

步骤2：按姓名分组排序，关联上一条记录的结束日期 #

按姓名分组后对所有时间段按起始日期排序，同时拉取同用户上一条记录的结束时间，用于判断间隔是否符合连续规则：

WITH sorted_records AS (
    SELECT 
        Name,
        [Date From] AS start_dt,
        [Date To] AS end_dt,
        LAG([Date To]) OVER (PARTITION BY Name ORDER BY [Date From]) AS prev_end
    FROM 你的业务表名
)

步骤3：标记连续块起点 #

判断当前记录是否属于新的连续块：

• 无前置记录 → 新块起点
• 当前段起始与前一段结束间隔1天 → 连续，不算新起点
• 间隔3天且前一段结束为周五、当前段起始为周一 → 跳过周末视为连续，不算新起点
• 其余情况均为新块起点

, block_flags AS (
    SELECT 
        Name,
        start_dt,
        end_dt,
        CASE 
            WHEN prev_end IS NULL THEN 1
            WHEN DATEDIFF(day, prev_end, start_dt) = 1 THEN 0
            WHEN DATEDIFF(day, prev_end, start_dt) = 3 
                AND DATEPART(WEEKDAY, prev_end) = 5 
                AND DATEPART(WEEKDAY, start_dt) = 1 
                THEN 0
            ELSE 1
        END AS is_new_block
    FROM sorted_records
)

步骤4：归组聚合得到最终连续块 #

对is_new_block做累加求和，同个累加值的记录属于同一个连续块，聚合后取最小起始、最大结束日期即可：

, block_groups AS (
    SELECT 
        Name,
        start_dt,
        end_dt,
        SUM(is_new_block) OVER (PARTITION BY Name ORDER BY start_dt ROWS UNBOUNDED PRECEDING) AS block_id
    FROM block_flags
)
SELECT 
    Name,
    MIN(start_dt) AS 连续块起始日期,
    MAX(end_dt) AS 连续块结束日期
FROM block_groups
GROUP BY Name, block_id
ORDER BY Name, 连续块起始日期;

方案验证 #

你给出的姓名A的测试示例代入后，会正确识别2021-12-23起始的跨周末时间段为同一个连续块，2021-11-08~2021-11-09的时间段因和其他段间隔不符合规则，单独成块，输出和示例完全一致。

可选补充：日历表适配方案 #

如果后续需要兼容法定假期等特殊规则，可引入日历表给每个日期打「非工作日标记」，判断两个时间段间隔内是否全为非工作日即可，核心逻辑和上述流程一致，仅调整is_new_block的判断条件即可。

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