MySQL:在两个日期之间创建事件的 "session"

Question

给定一个包含以下字段的表:

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

我正在尝试创建用户“ session ”，使用第一次作为“登录”时间，最后一次作为“注销”时间。结果表应该是:

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

在这种情况下，我的输出表中的 session 结束时间提前了几天。有关如何解决此问题的任何建议？

Answer 1

假设您的表名是 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