如何编写SQL统计员工非周末缺勤工作日天数？

嘿，这个需求我之前帮朋友处理过类似的，核心就是找出员工工作日出勤记录之间的“空白工作日”，然后统计数量。这里给你两种靠谱的方案，你可以根据实际场景选：

这种方法先通过LAG()窗口函数关联每条出勤记录的上一条出勤日期，再计算两个日期之间的工作日空白天数，最后汇总总数。

步骤1：获取带前序出勤日期的子查询 #

先筛选出John的非周末出勤记录，同时用LAG()函数把上一条的出勤日期关联到当前行：

SELECT 
    name,
    Date,
    -- 按姓名分组、日期排序，拿到上一条出勤的日期
    LAG(Date) OVER (PARTITION BY name ORDER BY Date) AS prev_attendance_date
FROM `time`
WHERE 
    name = 'John'
    AND DAYOFWEEK(Date) NOT IN (1, 7) -- 排除周日(1)和周六(7)
ORDER BY Date ASC;

步骤2：计算并汇总缺勤天数 #

基于上面的子查询，计算每条记录与前一条之间的工作日缺勤天数，最后求和得到总数：

SELECT 
    name,
    SUM(
        CASE 
            -- 第一条记录没有前序日期，缺勤数为0
            WHEN prev_attendance_date IS NULL THEN 0
            ELSE
                -- 先算两个日期之间的总间隔天数，减去其中的周末天数，再减1（因为两端都是出勤日）
                (DATEDIFF(Date, prev_attendance_date) - 1)
                -- 减去完整周的周末天数（每周2天）
                - FLOOR((DATEDIFF(Date, prev_attendance_date) - 1) / 7) * 2
                -- 处理跨周末的特殊情况：如果前序日期在周末后、当前日期在周末前，额外减2天
                - IF(DAYOFWEEK(prev_attendance_date) > DAYOFWEEK(Date), 2, 0)
                -- 处理前序日期是周日的情况（周日已被排除在出勤记录外，额外减1）
                - IF(DAYOFWEEK(prev_attendance_date) = 1, 1, 0)
                -- 处理当前日期是周六的情况（同理，周六已被排除，额外减1）
                - IF(DAYOFWEEK(Date) = 7, 1, 0)
        END
    ) AS total_absent_days
FROM (
    -- 嵌入步骤1的子查询
    SELECT 
        name,
        Date,
        LAG(Date) OVER (PARTITION BY name ORDER BY Date) AS prev_attendance_date
    FROM `time`
    WHERE 
        name = 'John'
        AND DAYOFWEEK(Date) NOT IN (1, 7)
) AS attendance_with_prev
GROUP BY name;

如果需要统计某个完整时间段（比如一整年、一个月）的工作日缺勤，这种方法逻辑更直观：先生成该时间段内的所有工作日，再减去John已经出勤的日期，剩下的就是缺勤天数。

示例：统计John 2018年全年的工作日缺勤天数 #

-- 递归生成2018年的所有日期
WITH RECURSIVE all_dates AS (
    SELECT '2018-01-01' AS calendar_date
    UNION ALL
    SELECT DATE_ADD(calendar_date, INTERVAL 1 DAY)
    FROM all_dates
    WHERE calendar_date < '2018-12-31'
)
-- 统计缺勤天数：全量工作日 - John的出勤工作日
SELECT COUNT(*) AS total_absent_days
FROM all_dates
WHERE 
    DAYOFWEEK(calendar_date) NOT IN (1, 7) -- 只保留工作日
    AND calendar_date NOT IN (
        SELECT Date 
        FROM `time` 
        WHERE name = 'John' 
        AND DAYOFWEEK(Date) NOT IN (1, 7)
    );

说明 #

• 如果要统计其他时间范围，只需要修改递归CTE里的起始和结束日期即可。
• 这种方法也能处理John在时间段内完全没有出勤的情况，会统计所有工作日的缺勤数。

两种方案可以按需选择：如果只关注已有出勤记录之间的空白，用方案一；如果需要统计固定时间段的整体缺勤，方案二更合适。

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