如何在MySQL中查询同一用户login后的首个open相邻行？

这个需求完全可以通过MySQL语句实现，以下提供两种适配不同版本的解决方案，同时支持添加额外筛选条件：

方法一：兼容所有MySQL版本（关联子查询） #

这种写法不依赖高版本窗口函数，适配所有MySQL环境：

SELECT o.*
FROM log l
JOIN log o 
  ON o.user = l.user 
  AND o.event = 'open' 
  AND o.timecreated > l.timecreated
WHERE l.event = 'login'
  -- 可添加login事件的筛选条件，比如限制login的context为0：AND l.context = 0
  AND NOT EXISTS (
    SELECT 1 
    FROM log o2 
    WHERE o2.user = l.user 
      AND o2.event = 'open' 
      AND o2.timecreated > l.timecreated 
      AND o2.timecreated < o.timecreated
  )
-- 可添加open事件的筛选条件，比如限制open的context为5或9：AND o.context IN (5, 9)
ORDER BY o.user, o.timecreated;

逻辑说明 #

1. 先定位所有event='login'的记录
2. 关联同一用户下、时间晚于该login的event='open'记录
3. 通过NOT EXISTS确保当前open是login之后第一个（即不存在其他open事件介于login和当前open之间）

方法二：MySQL 8.0+ 版本（窗口函数，性能更优） #

利用窗口函数可以更高效地处理这类序列匹配需求：

WITH user_events AS (
    SELECT 
        *,
        -- 计算当前行之前最近一次login事件的时间
        MAX(CASE WHEN event = 'login' THEN timecreated END) OVER (
            PARTITION BY user 
            ORDER BY timecreated 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_login_time
    FROM log
    WHERE event IN ('login', 'open')
    -- 可添加全局筛选条件，比如排除特定context：AND context != 10
),
open_ranked AS (
    SELECT 
        *,
        -- 对每个用户的每一次login后的open事件按时间排序
        ROW_NUMBER() OVER (
            PARTITION BY user, last_login_time 
            ORDER BY timecreated
        ) AS rn
    FROM user_events
    WHERE event = 'open' 
      AND last_login_time IS NOT NULL
      -- 可添加open事件的筛选条件：AND context = 5
)
SELECT id, event, user, context, timecreated
FROM open_ranked
WHERE rn = 1
ORDER BY user, timecreated;

逻辑说明 #

1. 第一个CTEuser_events：给每个事件标记出其所属的最近一次login事件时间
2. 第二个CTEopen_ranked：筛选出所有有对应login的open事件，按用户+最近login时间分区，对open事件按时间排序
3. 最终取每个分区中排序第一的记录，即为login之后紧邻的第一个open

验证示例数据 #

以上两种查询语句针对你提供的示例日志表，都能输出期望的匹配结果。

内容的提问来源于stack exchange，提问作者frumbert