MySQL合并连续/间隔单周末的假期段并统计超10天人员

要解决你的问题，核心是先把符合规则的假期段合并，再统计总天数。你的原代码只处理了相邻两段的关联，但没法处理多段连续合并的情况（比如750003的5-6月多段假期），需要用分组标记法来实现批量合并：

合并规则明确 #

需要合并的假期段满足以下任一条件：

• 后段假期的start_date = 前段假期的end_date + 1天（首尾连续）
• 前段假期的end_date是周五，后段假期的start_date是下周一（间隔仅周末）

完整SQL实现 #

WITH vacation_groups AS (
    -- 第一步：给每个假期段标记所属的合并组
    SELECT 
        marca,
        start_date,
        end_date,
        -- 计算组号：如果当前段和上一段需要合并，组号不变；否则组号+1
        SUM(CASE 
            WHEN prev_end_date IS NULL THEN 1  -- 第一条记录，初始组号1
            -- 判断是否需要合并：连续 或 隔周末（周五到下周一）
            WHEN start_date = DATE_ADD(prev_end_date, INTERVAL 1 DAY) 
                 OR (WEEKDAY(prev_end_date) = 4 AND WEEKDAY(start_date) = 0 AND DATEDIFF(start_date, prev_end_date) = 3)
            THEN 0
            ELSE 1
        END) OVER (PARTITION BY marca ORDER BY start_date) AS group_id
    FROM (
        -- 获取每个假期段的上一段end_date
        SELECT 
            marca,
            start_date,
            end_date,
            LAG(end_date) OVER (PARTITION BY marca ORDER BY start_date) AS prev_end_date
        FROM Vacations
    ) AS lagged_data
),
merged_vacations AS (
    -- 第二步：按marca和组号合并假期段
    SELECT 
        marca,
        MIN(start_date) AS start_date,
        MAX(end_date) AS end_date,
        -- 计算单段假期天数（含首尾）
        DATEDIFF(MAX(end_date), MIN(start_date)) + 1 AS segment_days
    FROM vacation_groups
    GROUP BY marca, group_id
)
-- 第三步：统计总天数并筛选超过10天的人员，同时输出合并后的假期段
SELECT 
    m.marca,
    m.start_date,
    m.end_date,
    t.total_days
FROM merged_vacations m
JOIN (
    SELECT marca, SUM(segment_days) AS total_days
    FROM merged_vacations
    GROUP BY marca
    HAVING SUM(segment_days) > 10
) t ON m.marca = t.marca
ORDER BY m.marca, m.start_date;

代码解释 #

1. vacation_groups CTE：通过LAG获取上一段假期的结束日期，然后判断当前段是否需要和上一段合并，用累加的方式生成组号，属于同一合并组的假期段会拥有相同的group_id。
   • WEEKDAY()函数：返回0=周一，4=周五，用来快速判断是否是周五接下周一的间隔周末场景。
2. merged_vacations CTE：按marca和group_id分组，取组内最早的start_date和最晚的end_date，得到合并后的完整假期段，并计算单段假期的天数（包含首尾日期）。
3. 最后一步：统计每个人员的总假期天数，筛选出总天数超过10天的记录，同时关联合并后的假期段信息，输出最终结果。

验证结果 #

执行上述SQL后，合并后的假期段会和你期望的一致，同时只保留总假期天数超过10天的人员数据：

• 750003的5-6月多段假期会合并为2022-05-16到2022-06-07，8月的两段连续假期合并为2022-08-02到2022-08-25
• 750002仅保留6月那段超10天的假期记录
• 750001的两段假期均超10天，全部保留

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