在一天mysql中获取总工作时间 [英] Get total hours worked in a day mysql

问题描述

我有一个MySQL表，其中记录了员工登录和注销时间。在输入列中，1表示登录，0表示注销。

  [id] [User_id] [Date_time] [in_out] 
 1 1 2011-01-20 09:30:03 1 
 2 1 2011-01-20 11:30:43 0 
 3 1 2011-01-20 11： 45:12 1 
 4 1 2011-01-20 12:59:56 0 
 5 1 2011-01-20 13:33:11 1 
 6 1 2011-01-20 15 ：38：16 0 
 7 1 2011-01-20 15:46:23 1 
 8 1 2011-01-20 17:42:45 0 
  

是否可以通过单一查询检索用户在一天内工作的总时间？

解决方案

  SELECT`User_id`，time（sum（`Date_time` *（1-2 *`in_out`）））
 FROM`whatever_table` GROUP BY`User_id`; 
  

（1-2 *`in_out`）术语给每个登录事件一个-1因子和每个注销事件一个+1因素。 sum 函数需要 Date_time 列的总和，GROUP BY`User_id`使每个不同的用户已创建。

I have a MySQL table where employee login and logout timings are recorded. Here in the in-out column 1-represents login and 0-represents logout.

  [id]   [User_id]           [Date_time]                 [in_out]
    1       1          2011-01-20 09:30:03                  1
    2       1          2011-01-20 11:30:43                  0
    3       1          2011-01-20 11:45:12                  1
    4       1          2011-01-20 12:59:56                  0
    5       1          2011-01-20 13:33:11                  1
    6       1          2011-01-20 15:38:16                  0
    7       1          2011-01-20 15:46:23                  1
    8       1          2011-01-20 17:42:45                  0

Is it possible to retrieve total hours worked in a day by a user using single query?

I tried a a lot but all in vain. I can do this in PHP using array but unable to do so using single query.

解决方案

SELECT `User_id`, time(sum(`Date_time`*(1-2*`in_out`)))
  FROM `whatever_table` GROUP BY `User_id`;

The (1-2*`in_out`) term gives every login event a -1 factor and every logout event a +1 factor. The sum function takes the sum of the Date_time column, and GROUP BY `User_id` makes that the sum for each different user is created.

这篇关于在一天mysql中获取总工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！