如何用SQL从VPN日志表关联用户的VPN连接起止时间？

你的test表存储VPN连接日志，包含以下字段：

• Username：文本类型，记录用户名
• Date：时间戳类型，记录日志发生时间
• Event：文本类型，'in'表示VPN连接，'out'表示VPN断开

要查询每个用户每次VPN连接的开始和结束时间，这里提供两种可靠的SQL实现方式：

方式一：使用窗口函数（推荐，高效简洁） #

利用LEAD()窗口函数，按用户分组、时间排序，直接匹配当前连接记录对应的下一条断开记录：

SELECT
    Username,
    Date AS DateStartVPN,
    LEAD(Date) OVER (PARTITION BY Username ORDER BY Date) AS DateFinishVPN
FROM test
WHERE Event = 'in'

• 逻辑：仅筛选Event = 'in'的连接记录，通过LEAD()取同一用户下时间紧随其后的日志时间作为断开时间。
• 特殊情况：如果用户有未断开的连接（只有in无对应out），DateFinishVPN会返回null，符合实际场景。

方式二：使用关联子查询（兼容不支持窗口函数的老版本数据库） #

通过子查询匹配当前连接记录之后最早的断开记录：

SELECT
    t1.Username,
    t1.Date AS DateStartVPN,
    (
        SELECT MIN(t2.Date)
        FROM test t2
        WHERE t2.Username = t1.Username
          AND t2.Date > t1.Date
          AND t2.Event = 'out'
    ) AS DateFinishVPN
FROM test t1
WHERE t1.Event = 'in'

• 逻辑：子查询限定了同一用户、时间晚于连接时间、事件为断开这三个条件，取最早的断开时间作为结束时间。你之前的子查询返回全null，大概率是遗漏了Event = 'out'的条件，或者时间范围的判断有误。

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