mysql - MySQL:在两个日期之间创建事件的“ session ”

给定具有以下字段的表：

username |  event                |  time (hh:mm:ss)
 user2   |   login               |   03:27:17
 user2   |   check messages      |   03:31:31
 user2   |   view profile        |   03:32:01
 user2   |   logout              |   03:32:48
 user3   |   login               |   13:00:59
 user3   |   change billing info |   13:03:11
 user3   |   logout              |   13:03:32

我试图创建用户“会话”，使用第一次作为“登录”时间，最后一次作为“注销”时间。结果表应为：

username |  event                |  login time  |  event time  |  logout time
 user2   |   login               |   03:27:17   |   03:27:17   |   03:32:48
 user2   |   check messages      |   03:27:17   |   03:31:31   |   03:32:48
 user2   |   view profile        |   03:27:17   |   03:32:01   |   03:32:48
 user2   |   logout              |   03:27:17   |   03:32:48   |   03:32:48
 user3   |   login               |   13:00:59   |   13:00:39   |   13:03:32
 user3   |   change billing info |   13:00:59   |   13:03:11   |   13:03:32
 user3   |   logout              |   13:00:59   |   13:03:32   |   13:03:32

我试过使用min（time）和max（time）来获取登录和注销时间，但当我这样做时，我会得到混合的登录、事件和注销时间。

SELECT login.eventTime, actualEvent.eventTime, logout.eventTime
FROM tableName login, tableName actualEvent, tableName logout
WHERE login.username = actualEvent.username
AND actualEvent.username = logout.username
AND login.eventTime =
   (SELECT MIN(minTime.eventTime)
   FROM tableName minTime
   WHERE minTime.username = login.username)
AND logout.eventTime =
   (SELECT MAX(maxTime.eventTime)
   FROM tableName maxTime
   WHERE maxTime.username = login.username)
AND login.eventTime < logout.eventTime;

任何帮助都将不胜感激。
编辑：
由@Bernd Buffen给出的答案很有效，除非时间发生在多个日期。例如：

username |  event                |  time (hh:mm:ss)
 user2   |   login               |   08/11/2015 03:27:17
 user2   |   check messages      |   08/11/2015 03:31:31
 user2   |   view profile        |   08/11/2015 03:32:01
 user2   |   logout              |   08/11/2015 03:32:48
 user3   |   login               |   08/11/2015 13:00:59
 user3   |   change billing info |   08/11/2015 13:03:11
 user3   |   logout              |   08/11/2015 13:03:32
 user2   |   login               |   08/12/2015 04:00:00
 user2   |   change billing info |   08/12/2015 04:03:22
 user2   |   logout              |   08/12/2015 04:08:17

在这种情况下，我的输出表的会话结束时间将提前几天。关于如何解决这个问题有什么建议吗？

最佳答案

假设您的表名是loggings，我认为这会有帮助（未选中）：

SELECT l.username, l.event, login_time, event_time, logout_time
FROM loggings l

LEFT JOIN (
    SELECT MIN(`time`) AS login_time, username FROM loggings
    GROUP BY username
) login_tbl
ON l.username=login_tbl.username

LEFT JOIN (SELECT `time` AS event_time, username FROM loggings) event_tbl
ON l.username=event_tbl.username

LEFT JOIN (
    SELECT MAX(`time`) AS logout_time, username FROM loggings
    GROUP BY username
) AS logout_tbl
ON l.username=logout_tbl.username

关于mysql - MySQL:在两个日期之间创建事件的“ session ”，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33421767/