在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）只取第一次签入时间和最后一次签出时间 - 这并不代表用户签入的时间长度（除非他一直处于签入状态）。我想要的是让SUM()对每个求和进行计算，然后按日添加它们。您有什么解决方法吗？

- Marcus Olsson

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

- Frank Schmitt

---

嗯，那可能行 - 但我认为查询会比那复杂一些，因为我不得不使用表别名之类的东西吧？ - Marcus Olsson

Answer 2

请尝试以下方法：

"Days" 的差异：

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

- Fredy

---

太好了！不过我必须将“TO_DAYS”更改为“TIME_IN_SEC”，并使用PHP计算小时数 - 我对天数不是很感兴趣。谢谢！ - Marcus Olsson

我明白了，请尝试使用上面的新查询。 - Fredy