SQL Server实现员工考勤出入记录单行展示及异常处理

嘿，这个需求我之前帮不少人捋过，咱们一步步拆解来搞：

先得明确咱们的考勤表大概结构哈，我先假设你的表叫attendance，字段大概是这样：employee_id（员工唯一标识）、record_time（考勤记录的时间戳）、record_type（记录类型，比如'IN'代表上班打卡，'OUT'代表下班打卡）——如果你的字段名不一样，直接替换就行。

核心思路：匹配成对打卡记录+处理异常 #

咱们用窗口函数来关联同一位员工的上下次打卡，这是处理这类成对记录的标准玩法：

• 给每条上班打卡（IN）记录，匹配紧随其后的同员工下班打卡（OUT）记录
• 遇到只有IN没有OUT的异常时，用自定义规则补全或标记

具体SQL示例（以MySQL为例） #

WITH employee_attendance AS (
    SELECT
        employee_id,
        record_time,
        record_type,
        -- 给每条IN记录匹配同天内的下一条OUT记录
        LEAD(record_time) OVER (
            PARTITION BY employee_id, DATE(record_time)
            ORDER BY record_time
        ) AS next_record_time,
        LEAD(record_type) OVER (
            PARTITION BY employee_id, DATE(record_time)
            ORDER BY record_time
        ) AS next_record_type
    FROM attendance
)
SELECT
    employee_id,
    record_time AS in_time,
    -- 处理异常：如果下一条记录不是OUT，就用当天18:00作为默认下班时间（可按需调整）
    CASE
        WHEN next_record_type = 'OUT' THEN next_record_time
        ELSE CONCAT(DATE(record_time), ' 18:00:00')
    END AS out_time,
    -- 计算工作时长（单位：分钟）
    TIMESTAMPDIFF(
        MINUTE,
        record_time,
        CASE
            WHEN next_record_type = 'OUT' THEN next_record_time
            ELSE CONCAT(DATE(record_time), ' 18:00:00')
        END
    ) AS work_duration_minutes,
    -- 标记异常状态
    CASE WHEN next_record_type != 'OUT' THEN '异常：无有效下班打卡记录' ELSE '正常' END AS attendance_status
FROM employee_attendance
WHERE record_type = 'IN' -- 只保留上班打卡记录，避免重复
ORDER BY employee_id, in_time;

关键细节说明 #

• 分区逻辑：我用employee_id+DATE(record_time)分区，是假设员工一天内的打卡是成对的（比如早IN午OUT、下午IN晚OUT），如果是夜班跨天场景，得调整分区规则（比如按打卡所属的排班周期）。
• 异常处理灵活性：如果不想用默认下班时间，也可以直接让out_time为NULL，然后在时长字段显示'未打卡下班'这类提示，完全按需调整。
• 其他数据库适配：
  • PostgreSQL：把TIMESTAMPDIFF换成EXTRACT(EPOCH FROM (out_time - in_time))/60，过滤异常可以用FILTER语法简化
  • SQL Server：用DATEDIFF(MINUTE, in_time, out_time)计算时长，核心逻辑完全一致
• 重复打卡处理：如果存在员工连续打两次IN的情况，可以先加一层去重逻辑，比如用ROW_NUMBER()过滤掉同一时间点的重复记录。

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