MySQL计算包含跨周末扩展的连续缺勤日期范围

这个需求确实有点棘手，尤其是要把跨周末的非连续日期但实际属于“连续扩展缺勤”的情况合并起来。我来分享一个用CTE和窗口函数实现的方案，亲测可以满足你的需求：

假设你的缺勤表结构 #

首先，假设我们有一个存储缺勤记录的表 absences，字段如下：

CREATE TABLE absences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

你给出的示例数据可以这样插入：

INSERT INTO absences (start_date, end_date) VALUES
('2016-08-26', '2016-08-26'),
('2016-08-29', '2016-09-04'),
('2016-09-05', '2016-09-11'),
('2016-09-12', '2016-09-18');

实现步骤 #

我们用递归CTE先把所有缺勤的工作日拆出来，再用窗口函数标记连续组，最后合并每组的日期范围：

WITH RECURSIVE all_absent_days AS (
    -- 递归生成所有缺勤范围内的日期，同时过滤掉周末
    SELECT start_date AS absent_day
    FROM absences
    UNION ALL
    SELECT DATE_ADD(absent_day, INTERVAL 1 DAY)
    FROM all_absent_days
    WHERE absent_day < (SELECT MAX(end_date) FROM absences)
      AND WEEKDAY(DATE_ADD(absent_day, INTERVAL 1 DAY)) BETWEEN 0 AND 4 -- 只保留周一到周五
),
ranked_days AS (
    -- 给每个缺勤工作日标记连续组ID
    SELECT 
        absent_day,
        SUM(CASE 
            WHEN LAG(absent_day) OVER (ORDER BY absent_day) IS NULL THEN 1
            -- 情况1：当前是周一，上一个是周五（跨周末连续）
            WHEN WEEKDAY(absent_day) = 0 AND WEEKDAY(LAG(absent_day) OVER (ORDER BY absent_day)) = 4 THEN 0
            -- 情况2：和上一个工作日连续（差1天）
            WHEN DATEDIFF(absent_day, LAG(absent_day) OVER (ORDER BY absent_day)) = 1 THEN 0
            -- 其他情况：新的组
            ELSE 1
        END) OVER (ORDER BY absent_day) AS group_id
    FROM all_absent_days
)
-- 按组合并日期范围
SELECT 
    MIN(absent_day) AS merged_start,
    MAX(absent_day) AS merged_end
FROM ranked_days
GROUP BY group_id
ORDER BY merged_start;

代码解释 #

1. all_absent_days CTE：递归生成所有缺勤范围内的日期，并且只保留周一到周五（排除周末），确保我们只处理实际计入缺勤的工作日。
2. ranked_days CTE：用LAG()窗口函数获取上一个缺勤的工作日，然后判断是否属于同一连续组：
   • 如果是第一个日期，直接开启新组；
   • 如果当前是周一，上一个是周五，视为连续，不开启新组；
   • 如果和上一个工作日差1天（连续工作日），视为同一组；
   • 其他情况（比如中间隔了工作日），开启新组。
3. 最后按group_id分组，取每组的最小和最大日期，就是合并后的连续缺勤范围。

测试结果 #

运行上面的代码，针对你的示例数据，会得到：

merged_start | merged_end
------------|------------
2016-08-26  | 2016-09-18

完全符合你想要的合并结果。如果有独立的缺勤（比如中间隔了工作日的情况），会单独生成一行记录。

补充说明 #

• 如果你的MySQL版本低于8.0，不支持窗口函数和递归CTE，可以用变量来模拟分组逻辑，但代码会复杂一些，不过8.0现在已经是主流版本了，建议升级。
• 确保你的日期字段都是DATE类型，避免时间部分干扰计算。

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