问题

以下是您请求的翻译结果：

我有一个记录用户事件的日志表，这些事件按时间戳排序。

| id | event    | user   |  context  | timecreated |
| --- | --------|--------|-----------|--------------- |
| 5  | login    | 234    | 0         | 65535 |
| 8  | open     | 234    | 5         | 65536 |
| 13 | open     | 234    | 9         | 65539 |
| 17 | open     | 234    | 8         | 65512 |
| 43 | login    | 128    | 0         | 65513 |
| 44 | login    | 293    | 0         | 65515 |
| 65 | open     | 293    | 5         | 65516 |
| 66 | open     | 234    | 5         | 65519 |
| 97 | login    | 567    | 0         | 65521 |
| 99 | open     | 567    | 9         | 65528 |

我想要一个查询，找到与相同用户ID的“login”事件相邻的第一个“open”事件行（和其他可能的筛选条件）。

伪示例：

select * from log where event='open' and context=5 and timecreated > (select timecreated from log where event='login' and user = (与上一个查询相同) limit 1).

在上面的表格中，这将匹配以下行：

| id | event    | user   |  context  | timecreated |
| --- | --------|--------|-----------|--------------- |
| 8  | open     | 234    | 5         | 65536
| 65 | open     | 293    | 5         | 65516
| 99 | open     | 567    | 9         | 65528

在MySQL语句中是否可能实现这一目标？

请注意，我已经按照您的要求排除了代码部分，只提供了翻译的文本。

答案1

得分: 2

尝试使用 LAG() 窗口函数：

SELECT *
FROM (
    SELECT *, LAG(event) OVER (PARTITION BY user ORDER BY timecreated) AS prev_event
    FROM log
) t
WHERE event = 'open'
AND prev_event = 'login'
AND context = 5

如果你使用的是 MySQL 版本小于 8，你可以使用以下查询：

SELECT o.*
FROM log o
JOIN log l
    ON l.event = 'login'
    AND o.user = l.user
    AND o.timecreated > l.timecreated
WHERE o.event = 'open'
AND o.context = 5
AND NOT EXISTS (
    SELECT 1 FROM log
    WHERE user = o.user
    AND timecreated > l.timecreated
    AND timecreated < o.timecreated
);

SELECT *
FROM (
    SELECT *, LAG(event) OVER (PARTITION BY user ORDER BY timecreated) AS prev_event
    FROM log
) t
WHERE event = 'open'
AND prev_event = 'login'
AND context = 5

SELECT o.*
FROM log o
JOIN log l
    ON l.event = 'login'
    AND o.user = l.user
    AND o.timecreated > l.timecreated
WHERE o.event = 'open'
AND o.context = 5
AND NOT EXISTS (
    SELECT 1 FROM log
    WHERE user = o.user
    AND timecreated > l.timecreated
    AND timecreated < o.timecreated
);

答案2

得分: 1

从测试表中选择所有记录，表别名为 t，条件是 t.event 等于 'open' 并且 t.user 等于
（从测试表中选择用户，条件是该用户的 id 等于（从测试表中选择最大 id，该 id 小于 t.id，并且事件等于 'login' 的记录））。

select * from test t
where t.event = 'open' and
    t.user =
   (select user
    from test
    where id = (select max(id) from test where  id < t.id) and event = 'login');