在mysql中计算最后一行的总时间持续时间

Question

我有这个mysql查询：

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      |
+----------------------------------------------------+---------------+

但我希望最后一行显示持续时间的总数，例如：

+----------------------------------------------------+---------------+
| 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      |
+----------------------------------------------------+---------------+

请问有人可以帮我修改查询语句，以便在下一行显示持续时间的总和吗？

- user1012135

---

请注意，如果可能的话，这可能更适合由应用程序逻辑处理，而不是由数据库查询处理。 - mellamokb

是的，@Jake的回答可行，我正在使用VB.NET制作一个登录应用程序，以跟踪学生在实验室内的总小时数。因此，管理员只需输入特定学生的用户ID，就可以检索出他们的所有日志，并在底部显示总计....:D - user1012135

Answer 1

尝试以下方法：

mysql> select *, timediff(end, start) as diff, sec_to_time(SUM(timediff(end,start))) as sum FROM timet GROUP BY start WITH ROLLUP;

+---------------------+---------------------+-------+----------+----------+
| start               | end                 | g     | diff     | sum      |
+---------------------+---------------------+-------+----------+----------+
| 2011-11-28 23:00:51 | 2011-11-28 23:00:56 | 0.678 | 00:00:05 | 00:00:05 |
| 2011-11-28 23:00:52 | 2011-11-28 23:00:57 | f     | 00:00:05 | 00:00:05 |
| 2011-11-28 23:00:53 | 2011-11-28 23:00:58 | 0.948 | 00:00:03 | 00:00:05 |
| 2011-11-28 23:00:58 | 2011-11-28 23:01:01 | 0.153 | 00:00:03 | 00:00:03 |
| NULL                | 2011-11-28 23:01:01 | 0.153 | NULL     | 00:00:18 |
+---------------------+---------------------+-------+----------+----------+
5 rows in set (0.01 sec)

MySQL：
使用Group by和rollup

- Amado Martinez

---

我有点困惑这是如何工作的。难道不可能在最后一个下面显示差的总和吗？ - user1012135

是的，通过从选择查询中删除差异列，您将只剩下总和列。我不知道为什么第3行的差异列显示3秒，而应该是5秒，总和列包含正确的值。WITH ROLLUP选项正好可以满足您的要求。它包括每个组函数（_sum_）的列总计行。 - Amado Martinez

Answer 2

我对这个答案并不是非常自豪，但应该可以工作：

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

- Jake Feasel

---

你好， 现在持续时间列中显示BLOB。你知道是什么问题吗？ 谢谢 - user1012135

跟随 @Amado 的答案 - 它更好。 - Jake Feasel

谢谢，现在可以工作了 :D ... 我只需要将 sum(duration) 更改为 CAST(SUM(Duration) AS Time)。 - user1012135

Answer 3

我理想情况下会将这种逻辑委托给第二个查询或应用层。

但是，尽我所能地解释你的查询，你可以尝试看看是否像这样产生你想要的结果：

SELECT
    CONCAT(u.lastname, ', ', u.firstname) AS Name,
    start.timestamp AS start,
    end.timestamp AS end,
    TIME(SUM(TIMEDIFF(end.timestamp, start.timestamp))) AS duration 
FROM user AS u
    INNER JOIN user_group AS ug ON u.user_bannerid = ug.user_bannerid
    INNER JOIN event AS start ON start.user_bannerid = u.user_bannerid AND start.status='In' AND start.group_id = ug.group_id
    INNER JOIN event AS end ON end.user_bannerid = u.user_bannerid AND end.status='Out' AND start.event_id = end.event_id
GROUP BY start.event_id WITH ROLLUP

http://dev.mysql.com/doc/refman/5.5/en/group-by-modifiers.html

编辑：注意到Amado已经给出了类似的解决方案，但我认为我的解决方案仍然相关。

- mikn

---

Amando的代码对我不起作用...我认为这是我需要的，但唯一的问题是结果没有显示出来，看起来缺少了什么，你能重写一下吗？ - user1012135

我真的必须明天交这个作业，如果有任何帮助，我将不胜感激。 - user1012135