如何编写SQL查询获取考勤表人员首次IN、末次OUT打卡记录

原表基础信息 #

• 存储表：考勤打卡表（示例SQL默认表名为attendance_records，可替换为实际业务表名）
• 原生字段：
  • Createtime：打卡实际发生时间
  • Department：员工所属部门
  • DeviceId：打卡设备唯一ID
  • DeviceName：打卡设备名称
  • UserCode：员工工号
  • UserName：员工姓名
  • DataPostingDateTime：打卡数据同步上传到系统的时间
  • RowID：打卡记录唯一行标识
• 样例数据参考：

  字段名	样例值
  Createtime	04-06-2022 18:33:00
  Department	ADMINISTRATION
  DeviceId	7101550000000
  DeviceName	SECOND GATE
  UserCode	DM00010
  UserName	ASHUTOSH SHARMA
  DataPostingDateTime	Jun 4 2022 6:34PM
  RowID	2354

返回字段映射规则 #

返回结果需包含6个字段，映射逻辑如下：

• Reference ID：直接取原表RowID字段值
• Created Time：直接取原表DataPostingDateTime字段值
• Employee Code：直接取原表UserCode字段值
• Punch Date：从Createtime字段提取的打卡日期（不含时间部分）
• Punch Time：从Createtime字段提取的打卡时间（不含日期部分）
• Punch Type：打卡类型，根据打卡时间判定为IN（上班卡）或OUT（下班卡），默认判定规则为当日12:00前打卡记为IN，12:00后打卡记为OUT，可根据企业实际考勤规则调整时间阈值。

核心筛选要求 #

最终结果仅保留两类有效打卡记录：

• 每位员工每个自然日的最早一条IN类型打卡记录
• 每位员工每个自然日的最晚一条OUT类型打卡记录

可落地SQL代码 #

以下代码基于MySQL 8.0及以上版本编写，支持窗口函数，核心逻辑可通用于大部分主流关系型数据库：

WITH punch_clean AS (
    -- 第一层CTE：完成字段映射、时间格式转换、打卡类型判定
    SELECT
        RowID AS `Reference ID`,
        DataPostingDateTime AS `Created Time`,
        UserCode AS `Employee Code`,
        -- 将字符串格式的打卡时间转为标准datetime类型，其他数据库可替换对应时间转换函数
        STR_TO_DATE(Createtime, '%d-%m-%Y %H:%i:%s') AS punch_datetime,
        DATE(STR_TO_DATE(Createtime, '%d-%m-%Y %H:%i:%s')) AS `Punch Date`,
        TIME(STR_TO_DATE(Createtime, '%d-%m-%Y %H:%i:%s')) AS `Punch Time`,
        CASE
            WHEN TIME(STR_TO_DATE(Createtime, '%d-%m-%Y %H:%i:%s')) < '12:00:00' THEN 'IN'
            ELSE 'OUT'
        END AS `Punch Type`
    FROM attendance_records
    WHERE Createtime IS NOT NULL AND UserCode IS NOT NULL
),
punch_ranked AS (
    -- 第二层CTE：按规则给打卡记录排序打标
    SELECT
        *,
        -- 同员工同天的IN记录按打卡时间升序排，排名1即为当日首次上班卡
        ROW_NUMBER() OVER (
            PARTITION BY `Employee Code`, `Punch Date`, `Punch Type`
            ORDER BY punch_datetime ASC
        ) AS first_in_rn,
        -- 同员工同天的OUT记录按打卡时间降序排，排名1即为当日末次下班卡
        ROW_NUMBER() OVER (
            PARTITION BY `Employee Code`, `Punch Date`, `Punch Type`
            ORDER BY punch_datetime DESC
        ) AS last_out_rn
    FROM punch_clean
)
-- 合并两类有效记录输出
SELECT `Reference ID`, `Created Time`, `Employee Code`, `Punch Date`, `Punch Time`, `Punch Type`
FROM punch_ranked
WHERE `Punch Type` = 'IN' AND first_in_rn = 1
UNION ALL
SELECT `Reference ID`, `Created Time`, `Employee Code`, `Punch Date`, `Punch Time`, `Punch Type`
FROM punch_ranked
WHERE `Punch Type` = 'OUT' AND last_out_rn = 1
ORDER BY `Employee Code`, `Punch Date`, `Punch Time`;

适配说明：如果使用其他数据库，仅需替换时间转换函数即可，核心筛选逻辑无需改动：

• SQL Server：将STR_TO_DATE(字段, '%d-%m-%Y %H:%i:%s')替换为CONVERT(DATETIME, 字段, 105)
• Oracle：将STR_TO_DATE(字段, '%d-%m-%Y %H:%i:%s')替换为TO_DATE(字段, 'DD-MM-YYYY HH24:MI:SS')
• 如果企业有固定上下班时段规则，直接修改Punch Type对应的CASE判定条件即可。

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