计算MySQL中最后一行的总持续时间 [英] Calculate the total time duration on last row in mysql
本文介绍了计算MySQL中最后一行的总持续时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个mysql查询:
I have this mysql query:
SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp start, end.timestamp end, timediff(end.timestamp, start.timestamp) duration
from user u, user_group ug, (
select *, (
select event_id from event L2 where L2.timestamp>L1.timestamp and L2.user_bannerid=L1.user_bannerid order by timestamp limit 1
) stop_id from event L1
) start join event end on end.event_id=start.stop_id
where start.status='In' and end.status='Out' and u.user_bannerid = start.user_bannerid and ug.user_bannerid = u.user_bannerid and ug.group_id = start.group_id
它显示了这样的内容:
+----------------------------------------------------+---------------+
| Name | start | end | duration |
+----------------------------------------------------+---------------+
| User | 2011-11-24 02:12:05 | 2011-11-24 02:12:20 | 00:00:15 |
| User | 2011-11-28 21:46:54 | 2011-11-28 21:53:01 | 00:06:17 |
+----------------------------------------------------+---------------+
但是我希望最后一行显示持续时间的总数,例如:
But i want the last row to show the total number of duration like:
+----------------------------------------------------+---------------+
| Name | start | end | duration |
+----------------------------------------------------+---------------+
| User | 2011-11-24 02:12:05 | 2011-11-24 02:12:20 | 00:00:15 |
| User | 2011-11-28 21:46:54 | 2011-11-28 21:53:01 | 00:06:17 |
| | | | 00:06:32 |
+----------------------------------------------------+---------------+
有人可以帮我修改查询,以在下一行显示持续时间总和吗?
Can someone please help me modify the query to show sum of the duration on the next row?
推荐答案
我对这个答案并不感到骄傲,但它应该可以工作:
I'm not super proud of this answer, but it should work:
SELECT 0 as is_total, CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp start, end.timestamp end, timediff(end.timestamp, start.timestamp) duration
from user u, user_group ug, (
select *, (
select event_id from event L2 where L2.timestamp>L1.timestamp and L2.user_bannerid=L1.user_bannerid order by timestamp limit 1
) stop_id from event L1
) start join event end on end.event_id=start.stop_id
where start.status='In' and end.status='Out' and u.user_bannerid = start.user_bannerid and ug.user_bannerid = u.user_bannerid and ug.group_id = start.group_id
UNION
SELECT 1, null, null, null, sum(duration)
FROM
(
SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name, start.timestamp start, end.timestamp end, timediff(end.timestamp, start.timestamp) duration
from user u, user_group ug, (
select *, (
select event_id from event L2 where L2.timestamp>L1.timestamp and L2.user_bannerid=L1.user_bannerid order by timestamp limit 1
) stop_id from event L1
) start join event end on end.event_id=start.stop_id
where start.status='In' and end.status='Out' and u.user_bannerid = start.user_bannerid and ug.user_bannerid = u.user_bannerid and ug.group_id = start.group_id
) total
ORDER BY is_total
这篇关于计算MySQL中最后一行的总持续时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文