mysql order by sum_在MySQL中每行使用SUM()

我正在构建一个非常简单的应用程序,可以在其中“完成”各种任务,为此,我在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()取每个和,然后将它们每天相加.

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

解决方法:

尝试这个：

“天”之间的差异：

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/20191201/2077806.html