SQL多行日期查询计算总工时：如何按打卡配对求DATEDIFF小时差

• 把orderinout为奇数的上班打卡记录，和同用户同日期下orderinout比它大1的偶数下班打卡记录做关联
• 配对成功的每一组计算时间差，转换为秒数求和后再转回时间格式，就是总工时

WITH daily_punch AS (
    SELECT 
        memberid,
        datesigned,
        orderinout
    FROM 替换为你的实际表名
    WHERE memberid = '987654321' 
      AND DATE(datesigned) = '2021-08-13' -- 按实际查询日期修改，也可保留dayname = 'Friday'条件
)
SELECT 
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t2.datesigned, t1.datesigned)))) AS total_work_hours
FROM daily_punch t1
INNER JOIN daily_punch t2 
    ON t1.memberid = t2.memberid 
    AND t2.orderinout = t1.orderinout + 1
WHERE t1.orderinout % 2 = 1;

SELECT 
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t2.datesigned, t1.datesigned)))) AS total_work_hours
FROM 替换为你的实际表名 t1
INNER JOIN 替换为你的实际表名 t2
    ON t1.memberid = t2.memberid 
    AND DATE(t1.datesigned) = DATE(t2.datesigned)
    AND t2.orderinout = t1.orderinout + 1
WHERE t1.memberid = '987654321'
  AND DATE(t1.datesigned) = '2021-08-13'
  AND t1.orderinout % 2 = 1;

小提示：用DATE(datesigned)过滤比直接用dayname更准确，避免把不同周的同一星期数据误统计。如果你的表中dayname已经和日期一一对应可以保留原过滤条件。

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