如何从attlog表的多笔打卡记录生成人员月度考勤报表？

前提假设 #

假设attlog表包含核心字段：

• user_id：人员唯一标识
• check_time：打卡日期时间（如datetime类型）
• check_type：打卡类型（可选，比如'IN'为上班、'OUT'为下班，或用数字0/1区分）

场景1：表中包含明确的进出类型字段 #

如果打卡记录自带check_type标识，可直接按人员、日期分组，提取每日的首次上班和末次下班时间生成月度报表：

SELECT
    user_id,
    DATE_FORMAT(check_time, '%Y-%m') AS report_month,
    DATE(check_time) AS check_date,
    MIN(CASE WHEN check_type = 'IN' THEN check_time END) AS first_in_time,
    MAX(CASE WHEN check_type = 'OUT' THEN check_time END) AS last_out_time
FROM attlog
-- 筛选目标月度，示例为2024年5月
WHERE DATE_FORMAT(check_time, '%Y-%m') = '2024-05'
GROUP BY user_id, report_month, check_date
ORDER BY user_id, check_date;

场景2：无明确进出类型，按时间顺序配对进出 #

如果打卡记录没有类型标识，默认按时间顺序将奇数条记录判定为上班、偶数条为下班，可通过窗口函数分组配对：

WITH ranked_attlog AS (
    SELECT
        user_id,
        check_time,
        DATE(check_time) AS check_date,
        -- 按人员、日期分组，给打卡记录排序
        ROW_NUMBER() OVER (PARTITION BY user_id, DATE(check_time) ORDER BY check_time) AS rn
    FROM attlog
    WHERE DATE_FORMAT(check_time, '%Y-%m') = '2024-05'
)
SELECT
    user_id,
    DATE_FORMAT(check_time, '%Y-%m') AS report_month,
    check_date,
    MAX(CASE WHEN rn % 2 = 1 THEN check_time END) AS in_time,
    MAX(CASE WHEN rn % 2 = 0 THEN check_time END) AS out_time
FROM ranked_attlog
GROUP BY user_id, report_month, check_date, CEIL(rn / 2)
ORDER BY user_id, check_date, in_time;

补充说明 #

• 若需显示人员姓名，可关联人员信息表（如user_info），通过user_id关联查询user_name字段
• 可根据实际需求调整日期格式、筛选条件，或添加异常判断（如无下班记录的标记）

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