如何编写SQL统计员工每日4次打卡的上下班时间与总工作时长

现有问题梳理 #

• 字段引用错误：你的表名是record不是attendance，员工编号字段为employee_code而非badge_id，打卡时间字段为created而非timestamp
• 条件逻辑错误：WHERE条件中将status字段的判断错误写为时间字段判断，A1.timestamp = 'check_in'属于明显的字段混淆
• 自连接逻辑缺陷：使用INNER JOIN关联会产生笛卡尔积，每名员工单日有2条check_in、2条check_out数据，会匹配生成4条结果，无法直接得到单日最早签到、最晚签退的聚合结果

修复后可用SQL #

直接使用分组聚合逻辑即可实现需求，不需要自关联：

SELECT
  employee_code,
  MIN(IF(status = 'check_in', created, NULL)) AS check_in_at,
  MAX(IF(status = 'check_out', created, NULL)) AS check_out_at,
  TIMEDIFF(MAX(IF(status = 'check_out', created, NULL)), MIN(IF(status = 'check_in', created, NULL))) AS total_time
FROM record
WHERE DATE(created) = '2021-11-22'
GROUP BY employee_code, DATE(created);

逻辑说明 #

按员工编码、打卡日期分组后，用IF函数分别筛选不同打卡状态的时间，取签到时间的最小值即为当日最早签到时间，取签退时间的最大值即为当日最晚签退时间，最后通过TIMEDIFF计算两个时间的差值即可得到总工作时长。

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