在MySQL中每行使用SUM()

Question

我正在构建一个非常简单的应用程序，我可以在其中“打卡”执行各种任务，为此，我用MySQL编写了一个数据库表，如下所示：

|ID|user_id |task_id|checkin_time       |checkout_time
------------------------------------------------------------
|31|2       |289    |2012-07-12 09:50:03|2012-07-12 09:51:27
|32|2       |289    |2012-07-12 10:00:05|2012-07-12 13:00:05

对于SQL查询，我想要得到的是上周用户每天签入的总时间。我尝试了这个查询：

SELECT COUNT( id ) AS time_id, SUM( checkout_time - checkin_time ) AS total_time, DATE( checkout_time ) AS checkout_day
FROM time_table
WHERE user_id =  '2'
AND checkout_time >= ( DATE_SUB( CURDATE( ) , INTERVAL 1 WEEK ) ) 
GROUP BY checkout_day
ORDER BY checkout_day ASC

这给出了一个非常好的结果，但如果每天有超过一次的“签到”-因为SUM( checkout_time - checkin_time )使用第一个checkin_time，最后一个checkout_time -这不一定代表用户被签入的时间(除非他100%的时间被签入)，就会有一些严重的复杂性。

我想要的是SUM()获取每个和，然后将它们相加，作为每天的基础。

你有什么办法解决这个问题吗？

Answer 1

试试这个：

“天”的差异：

SELECT COUNT( id ) AS time_id, SUM( TO_DAYS(checkout_time) - TO_DAYS(checkin_time) ) AS total_time, DATE( checkout_time ) AS checkout_day
FROM time_table
WHERE user_id =  '2'
AND checkout_time >= ( DATE_SUB( CURDATE( ) , INTERVAL 1 WEEK ) ) 
GROUP BY checkout_day
ORDER BY checkout_day ASC

“秒”的差异：

SELECT COUNT( id ) AS time_id, SUM( UNIX_TIMESTAMP(checkout_time) - UNIX_TIMESTAMP(checkin_time) ) AS total_time, DATE( checkout_time ) AS checkout_day
    FROM time_table
    WHERE user_id =  '2'
    AND checkout_time >= ( DATE_SUB( CURDATE( ) , INTERVAL 1 WEEK ) ) 
    GROUP BY checkout_day
    ORDER BY checkout_day ASC

Answer 2

使用子查询获取时间差，然后求和：

SELECT 
  COUNT( id ) AS time_id, 
  SUM( time_diff ) AS total_time, 
  DATE( checkout_time ) AS checkout_day
FROM (
  select 
    id, 
    checkout_time - checkin_time as time_diff, 
    checkout_time
  from time_table)
WHERE user_id =  '2'
AND checkout_time >= ( DATE_SUB( CURDATE( ) , INTERVAL 1 WEEK ) ) 
GROUP BY checkout_day
ORDER BY checkout_day ASC