mysql - 使用mysql计算每天的工作时间

Question

我想使用 MySQL 计算每天的工作时间。我展示了很多小时的计算解决方案，但没有一个符合我的要求。在我的 table 上我没有在我们的外场。我必须考虑第一次进入和第二次进入并根据它计算工作时间。

表结构:

CREATE TABLE IF NOT EXISTS `timesheet` (
  `MachineNo` int(6) unsigned NOT NULL,
  `Empcardno` int(3) unsigned NOT NULL,
  `Date` date  NOT NULL,
  `Time` time  NOT NULL
) DEFAULT CHARSET=utf8;

INSERT INTO `timesheet` 
(`MachineNo`, `Empcardno`, `Date`,`Time`) VALUES
    (01,    5,  '2020-05-22',   '18:15:54'),    
    (01,    5,  '2020-05-22',   '14:46:47'),
    (01,    5,  '2020-05-22',   '14:26:05'),
    (01,    5,  '2020-05-22',   '09:26:30'),
    (01,    5,  '2020-05-21',   '18:15:45'),
    (01,    5,  '2020-05-21',   '14:48:39'),
    (01,    5,  '2020-05-21',   '14:29:55'),
    (01,    5,  '2020-05-21',   '09:37:49');    

MySql fiddle link

我尝试了以下查询，但它只给了我最大和最小时间之间的总小时数。它不考虑所有的输入输出值。我想考虑所有进出点之间的时间。

SELECT
  Empcardno,min(Time),
  max(Time),
  TIMEDIFF(max(Time),
  min(Time)) As Diff_Value
FROM
  timesheet
GROUP BY
  DATE(Date),Empcardno

结果

   Empcardno    Date       min(Time)    max(Time)   Diff_Value
    5         2020-05-21    09:37:49    18:15:45    08:37:56
    5         2020-05-22    09:26:30    18:15:54    08:49:24

但我想计算时间作为这两者之间的时间

 (01,    5,  '2020-05-21',   '14:29:55'),
 (01,    5,  '2020-05-21',   '09:37:49'); 

和

(01,    5,  '2020-05-21',   '18:15:45'),
(01,    5,  '2020-05-21',   '14:48:39'),

Answer 1

如果没有窗口函数，这是一个棘手的问题……基本上，您需要为每个日期的每台机器的每个条目生成行号。然后，您可以组合奇数行和偶数行以生成时差，可以将其相加以生成当天的总时间。为此，我已将奇数行上的时间转换为负数，以便将该值添加到下一个值(来自偶数行)时，我们得到两者之间的差异。

SELECT MachineNo, Empcardno, Date, SEC_TO_TIME(SUM(tsecs)) AS total_time
FROM (
  SELECT CASE WHEN MachineNo = @mn AND Empcardno = @en AND `Date` = @dt
              THEN @rn := @rn + 1
              ELSE @rn := 1
         END AS rn,
         @mn := MachineNo AS MachineNo, @en := Empcardno AS Empcardno, 
         @dt := `Date` AS `Date`,
         CASE WHEN @rn % 2 = 1 THEN -TIME_TO_SEC(`Time`)
              ELSE TIME_TO_SEC(`Time`)
         END AS tsecs
  FROM timesheet
  CROSS JOIN (SELECT @mn := 0, @en := 0, @dt := '', @rn := 0) init
  ORDER BY Date, Time
) t
GROUP BY MachineNo, Empcardno, Date

输出:

MachineNo   Empcardno   Date        total_time
1           5           2020-05-21  08:19:12
1           5           2020-05-22  08:28:42

Demo on SQLFiddle