MySQL按特定用户统计工作时长：跨行存储的打卡记录如何计算时间差

完全可以实现，核心是把同用户同一天的进场、退场两条记录关联到同一行，再做时间差值计算即可，具体实现如下：

前提说明 #

你提供的表结构中各字段作用默认如下：

• user_id：用户唯一标识
• added_on：打卡的完整时间（包含日期+时分秒）
• status_enter：值为1代表进场打卡
• status_exit：值为1代表退场打卡

实现逻辑 #

• 先拆分出两类打卡子集：进场打卡子集（status_enter=1）、退场打卡子集（status_exit=1）
• 用user_id和打卡日期作为关联条件，把同用户同一天的进场、退场记录合并到同一行
• 用MySQL内置的时间函数计算退场时间与进场时间的差值，得到工作时长

参考SQL语句 #

SELECT 
  enter.user_id,
  DATE(enter.added_on) AS 工作日期,
  enter.added_on AS 进场时间,
  exit.added_on AS 退场时间,
  -- 这里计算的是分钟级时长，可把MINUTE换成SECOND/HOUR得到对应单位的时长
  TIMESTAMPDIFF(MINUTE, enter.added_on, exit.added_on) AS 工作时长_分钟
FROM 
  -- 筛选所有进场打卡记录，把「你的表名」替换为实际表名即可
  (SELECT user_id, added_on FROM 你的表名 WHERE status_enter = 1) AS enter
INNER JOIN 
  -- 筛选所有退场打卡记录
  (SELECT user_id, added_on FROM 你的表名 WHERE status_exit = 1) AS exit
ON 
  enter.user_id = exit.user_id 
  AND DATE(enter.added_on) = DATE(exit.added_on)
-- 按需添加筛选条件，比如指定用户和日期
-- WHERE enter.user_id = 'John' AND DATE(enter.added_on) = '2021-06-25'

异常场景适配 #

• 若存在同用户一天多次进出的情况，可以额外增加逻辑取当日最早进场时间、最晚退场时间，或者按打卡顺序配对相邻的进出记录
• 若存在缺卡情况（只有进场没退场/只有退场没进场），可以把INNER JOIN换成LEFT JOIN，就能识别出缺卡的异常记录

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