每名員工每天的總工作時間，包括通宵工作時間

我有一張這樣的桌子：

CREATE TABLE Table1
(ID int, empid int, time datetime, state int);

+--------------+---------------------+-----------------+
|    empid     |       time          |      state      | 
+--------------+---------------------+-----------------+
(      4       | 2014-03-01 11:12:00 |         0       )  
(      5       | 2014-03-01 12:28:06 |         0       )
(      4       | 2014-03-01 12:50:07 |         1       )
(      4       | 2014-03-01 13:38:00 |         0       )
(      5       | 2014-03-01 13:28:06 |         1       )
(      4       | 2014-03-01 18:42:15 |         1       )
(      4       | 2014-03-02 08:11:08 |         0       ) 
(      4       | 2014-03-02 13:26:11 |         1       ) 
(      5       | 2014-03-02 14:16:15 |         0       ) 
(      4       | 2014-03-02 16:16:15 |         0       ) 
(      5       | 2014-03-02 17:48:21 |         1       ) 
(      4       | 2014-03-02 19:39:03 |         1       ) 
(      5       | 2014-03-02 20:16:15 |         0       )
(      5       | 2014-03-03 04:16:15 |         1       )
+--------------+---------------------+-----------------+

為簡潔起見省略了主鍵 - 請參閱http://www.sqlfiddle.com/#!9/30503/1

0 表示登錄 - 1 表示註銷。例如，如果員工通宵工作（如員工 5 在 2014-03-02 和 2014-03-03 之間所做的那樣），則每個使用者每天或幾天內可以有多次登錄/註銷

我正在嘗試獲取每位員工每天/輪班的總工作時間（例如，某些輪班將跨越兩天）：

1      2014-03-02   total hours worked    08:32:00

我想出了這個查詢：

SELECT CONCAT(
MOD(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state =    '0' THEN time END)), 24 ), ' hours ',
MINUTE(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state    = '0' THEN time END))), ' minutes') as HoursWorked, 
empid, 
Date(time)
FROM emplog T
GROUP BY empid, Date(time)

這只給了我最小登錄和最大註銷之間經過的時間。

從較早的問題中，我了解瞭如何獲取每位員工每天的總工作時間，其中不包括夜班：

select empid, work_dt,
   SEC_TO_TIME(sum(TIMESTAMPDIFF(SECOND,login,logout))) as time_worked
from (
select empid, date(time) as work_dt, time as login
   , coalesce(
         (select min(time) 
          from emplog as b 
          where a.empid = b.empid 
            and date(a.time) = date(b.time)
            and b.time >= a.time 
            and b.state = 1
         ), now()) as logout
from emplog as a 
where a.state = 0
) as t
group by empid, work_dt;

仍然沒有想出一種方法來考慮夜班 - 期待您的意見。

週末會有員工上班。假期不會有問題；夏令時也不會。

應用程序沒有檢查以防止時鐘不同步？例如，打卡/簽出記錄不按時間順序或由於員工錯誤而重複。我已經在我編寫的用於連接設備並檢索原始數據的程式碼中處理了這些類型的問題 - 可能更好地處理我的程式碼中的所有計算。

夜班從輪班開始之日算起 100%。例如，如果您在午夜之前的 02.01 開始工作，則以 02.01 作為日期保存總工作時間。如果您在午夜之後開始，則總工作時間會以 03.01 作為日期保存。

如果沒有匹配的註銷，我會在為 MySQL 數據庫準備原始數據的程式碼中以預定義的小時數手動註銷使用者。

請檢查性能是否足夠：

SELECT empid
     ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                     FROM emplog b
                                                    WHERE b.empid = a.empid
                                                      AND b.time  > a.time
                                                      AND b.state = 1
                                                   )))) date_worked
 FROM emplog a
WHERE state=0
GROUP BY empid;

編輯

每天匯總：

SELECT empid
     ,DATE(time) day
     ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                     FROM emplog b
                                                    WHERE b.empid = a.empid
                                                      AND b.time  > a.time
                                                      AND b.state = 1
                                                   )))) date_worked
 FROM emplog a
WHERE state=0
GROUP BY empid, DATE(time);

引用自：https://dba.stackexchange.com/questions/130676