如何根据员工工作时长核算薪资？SQL查询出现负值错误如何解决

问题原因 #

• 聚合逻辑错误
  你当前的写法是先对所有工作时长差值求和，再乘以pago_jobber字段，但pago_jobber不在GROUP BY分组字段中，也没有加聚合函数，数据库会随机取该员工某一条任务记录的小时薪资参与计算，若同一员工不同任务的小时薪资有差异，计算结果必然出错。正确逻辑应该是先计算单条任务的薪资（单条时长×单条对应的小时薪资），再对所有单条薪资求和得到总薪资。
• 时间差计算方式错误
  直接对两个时间类型字段做减法，在绝大多数数据库中返回的不是小时单位的时长差值，而是类似「时间数值化后的整数差」，比如2024-05-20 10:00减2024-05-20 09:00，你预期是1，但直接减可能返回10000（小时差的数值放大），如果有跨天的打卡记录或者打卡录入错误（下班时间早于上班时间），还会直接出现负值。
  你需要用数据库内置的时间差函数将两个时间的差值转换为小时单位，比如MySQL用TIMESTAMPDIFF(HOUR, 开始时间, 结束时间)，PostgreSQL用EXTRACT(EPOCH FROM (结束时间 - 开始时间))/3600。如果要排除异常打卡的负时长，可以套一层GREATEST(时间差结果, 0)把负值转为0。
• GROUP BY语法不规范
  部分数据库开启严格模式后，SELECT中出现的非聚合字段必须全部包含在GROUP BY中，你当前的写法本身就不符合SQL标准，也会带来计算结果不可控的问题。

修正后的SQL示例（以MySQL为例） #

SELECT 
  t.id_usuario as jobber_id,
  COUNT(DISTINCT t.nombre_orden_trabajo) as total_tareas,
  SUM(
    -- 计算单条任务的有效小时薪资，异常负时长按0计算
    GREATEST(TIMESTAMPDIFF(HOUR, t.HORA_INICIO, t.HORA_TERMINO), 0) * t.pago_jobber
  ) as pago_jober
FROM tareas t 
INNER JOIN usuarios u ON t.id_usuario = u.id_usuario
WHERE u.PAIS = "CL"
GROUP BY t.id_usuario;

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