我正在构建一个非常简单的应用程序,可以在其中“完成”各种任务,为此,我在MySQL中有了一个数据库表,看起来像这样：
|ID|user_id |task_id|checkin_time |checkout_time
------------------------------------------------------------
|31|2 |289 |-07-12 09:50:03|-07-12 09:51:27
|32|2 |289 |-07-12 10:00:05|-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()取每个和,然后将它们每天相加.
关于如何解决此问题的任何想法？
解决方法:
尝试这个：
“天”之间的差异：
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
标签：sql,mysql,database
来源： https://codeday.me/bug/1201/2077806.html
