mysql - 我将如何更新此 MySQL 查询以获取从开始、停止、暂停和恢复事件的总经过时间

Question

我有这个查询，我可以通过搜索 SOF 创建。

SELECT
    start_log.reportID,
    start_log.userID,
    start_log.testID,
    MAX(start_log.eventDateTime) AS start_time,
    end_log.eventDateTime AS end_time,
    TIMESTAMPDIFF(MINUTE, MAX(start_log.eventDateTime), end_log.eventDateTime) AS diff
FROM
    testtracker_event AS start_log
INNER JOIN
    testtracker_event AS end_log ON (
            start_log.userID = end_log.userID
                AND
            start_log.reportID = end_log.reportID
                AND
            start_log.testID = end_log.testID
        AND
            end_log.eventDateTime > start_log.eventDateTime)
WHERE start_log.eventType = 'start'
AND end_log.eventType = 'finish'
AND start_log.testID = 2
AND start_log.userID = '$user'
GROUP BY
    start_log.userID,
    start_log.testID,
    start_log.eventDateTime,
    start_log.reportID
ORDER BY
--  userID DESC,
    start_time DESC

按预期工作，查询返回与列的start_time时间end_time差diff。我现在想扩展此查询以考虑暂停和恢复事件我只是无法理解如何将这些与当前查询考虑在内。我可以看到扩展选择以解释暂停和恢复列pause_time，resume_time除了我将如何解释可能有多个暂停和恢复事件的情况？我还冒昧地猜测我可能需要做一些额外的计算才能得到实际经过的时间，但我也不知道我会如何引用这些。

任何帮助将非常感激。

样本数据

reportID|testID|eventDateTime      |eventType  
--------|------|-------------------|----------  
11881   |  2   |2013-08-16 14:19:09|start  
11910   |  2   |2013-08-16 14:48:04|start  
11910   |  2   |2013-08-16 14:55:08|finish  
11891   |  2   |2013-08-16 15:01:01|start  
11891   |  2   |2013-08-16 15:25:42|finish  
11888   |  2   |2013-08-16 16:20:27|start  
11888   |  2   |2013-08-16 16:51:16|finish  
11889   |  2   |2013-08-19 09:00:13|start  
11889   |  2   |2013-08-19 10:31:17|finish  
11905   |  2   |2013-08-19 12:10:00|start  
11898   |  2   |2013-08-19 12:12:02|start  
11898   |  2   |2013-08-19 12:19:28|finish  
11905   |  2   |2013-08-19 12:27:21|finish  
11880   |  2   |2013-08-19 16:40:26|start  
11880   |  2   |2013-08-19 16:40:27|finish  
11895   |  2   |2013-08-19 16:42:58|start  
11895   |  2   |2013-08-19 16:55:14|finish  
11908   |  2   |2013-08-20 09:46:11|start  
11908   |  2   |2013-08-20 09:56:20|finish  
11862   |  2   |2013-08-20 10:20:11|start  
11862   |  2   |2013-08-20 10:29:44|finish  
11911   |  2   |2013-08-20 11:31:33|start  
11911   |  2   |2013-08-20 11:59:15|finish  
11913   |  2   |2013-08-20 12:44:07|start  
11913   |  2   |2013-08-20 13:32:52|finish  
11928   |  2   |2013-08-20 14:28:38|start  
11928   |  2   |2013-08-20 14:40:00|finish  
11836   |  2   |2013-08-20 16:26:03|start  
11887   |  2   |2013-08-20 16:30:36|start  
11836   |  2   |2013-08-20 16:43:53|finish  
11887   |  2   |2013-08-20 16:58:44|pause  
11902   |  2   |2013-08-22 14:51:53|start  
11902   |  2   |2013-08-22 15:01:05|finish  
11912   |  2   |2013-08-22 15:01:43|start  
11912   |  2   |2013-08-22 15:12:21|finish  
11930   |  2   |2013-08-22 16:09:22|start  
11814   |  2   |2013-08-14 10:39:07|finish  
11930   |  2   |2013-08-23 09:55:14|finish  
11914   |  2   |2013-08-26 14:55:04|start  
11914   |  2   |2013-08-26 15:19:48|pause  
11940   |  2   |2013-08-27 07:44:12|start  
11940   |  2   |2013-08-27 07:55:52|finish  
11914   |  2   |2013-08-27 09:13:28|resume  
11914   |  2   |2013-08-27 09:13:44|pause  
11914   |  2   |2013-08-27 09:15:56|resume  
11914   |  2   |2013-08-27 09:30:12|finish  

从样本中删除 userId 列以保护身份。

不是预期的结果reportID - 11914

reportID|testID|start|end|diff  
--------|------|-----|---|-----  
11914 | 2 |2013-08-26 14:55:04|2013-08-27 09:30:12|1115  

预期结果reportID - 11902

reportID|testID|start|end|diff  
--------|------|-----|---|-----  
11902 | 2 | 2013-09-10 14:26:53 | 2013-09-10 14:45:36 | 18

标签： mysqlsql