计算总加班时间 MySQL

Question

我正在创建一个时钟系统和管理网站。我正在尝试使用 MySQL 查询来获取特定部门的加班时间。

时钟更新名为 events 的 MySQL 表，该表存储员工 ID、他们正在从事的工作、time_in 和 time_out 。我可以生成一个包含每个 employee_id 的总工作时间的表格：

SELECT employee_id, 
       SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))) AS hours 
  FROM events 
 GROUP BY employee_id;

这将返回一个包含 employee_id 及其工作总时间的表格。为了创建一个employee_id加班表，我使用：

SELECT employee_id,
       IF(HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))>40,
          HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))-40,
          0) AS overtime 
  FROM events 
  GROUP BY employee_id;

这将返回一个employee_id表以及他们加班的时间。

当我想找到所有员工的加班总数时，我遇到了麻烦。我以为我可以将 IF 函数放在 SUM() 内查找超时，但是当我运行以下语句时：

SELECT SUM(IF(HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))>40,
              HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))-40,
              0)) AS overtime 
 FROM events;

我收到此错误：

ERROR 1111 (HY000): Invalid use of group function

我有对此进行了全面查找，但几乎没有发现 SUM(IF(SUM())) 函数。

注意：加班费必须单独计算，我不能只采用总工作时间/员工人数 - 40。如果一名工人每周工作 30 小时，另一名工人每周工作 50 小时，则有 10 小时的加班时间，但平均而言没有加班时间。

Answer 1

这个怎么样？

SELECT SUM(overtime)
  FROM
  (
    SELECT employee_id,
           IF(HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))>40,
              HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))-40,
              0) AS overtime 
      FROM events 
     GROUP BY employee_id
  )TOTAL

当您使用 SQL 实现复杂的业务逻辑时，嵌套查询具有一个关键优势：它们使您可以测试逻辑的每个级别并以可靠的方式构建它。

Answer 2

只需将其添加到 Ollie 的上述答案中即可：

SELECT SUM(overtime)
  FROM
  (
    SELECT employee_id,
           IF(HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))>40,
              HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))-40,
              0) AS overtime 
      FROM events
     GROUP BY employee_id
  ) TOTAL_OVERTIME

您需要命名派生表以使代码正常工作。