mysql - 在 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 – 这并不一定代表用户 checkin 了多少时间(除非他 100% 的时间都签到了)。

我想要的是让 SUM() 计算每笔金额，然后每天将它们相加。

关于如何解决这个问题的任何想法？

Answer 1

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

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