mysql - 在子查询 where 子句中引用列

Question

我有一个表，其中包含多个 session 事件的记录。每一行都是一个 session 中的一个事件，一个 session 可以有多个相同的事件。这些基本上是游戏环节，每个事件都是回合开始或回合结束。我的数据看起来像

Session_id | Event_type | Event_Time
1 | round_start | 12:01:00
1 | round_end | 12:02:00
1 | round_start| 12:05:00
1 | round_end | 12:7:00
2 | round_start | 14:11:00
2 | round_end | 14:12:00
3 | round_start| 15:09:00
3 | round_end | 15:13:00

我正在尝试找出平均回合持续时间。我尝试了以下 SQL

select
RS.session_id,
RS.Event_Time as StartTime,
RE.EndTime,
TIMESTAMPDIFF(MINUTE,RE.EndTime,RS.Event_Time) as duration
from amp_event_mamlaka as RS
left join 
(
    select session_id, min(event_time) as EndTimd from amp_event_mamlaka 
    where Event_Type = "Round End" and session_id = RS.session_id and  event_time>RS.Event_Time
) RE
on RE.session_id = RS.session_id

问题是我无法在联接表中引用 RS.session_id 和 RS.event_time。

我正在使用 MySQL。关于如何实现这一点有什么建议吗？

谢谢

Answer 1

我建议您使用相关子查询来解决这个问题:

select RS.session_id, RS.Event_Time as StartTime,
       (select smin(event_time) 
        from amp_event_mamlaka em
        where em.session_id = RS.session_id and
              em.Event_Type = 'Round End' and 
              em.event_time > RS.Event_Time
       ) as EndTime,
from amp_event_mamlaka RS;

您可以使用子查询进行时间戳差异:

select RS.*, TIMESTAMPDIFF(MINUTE, EndTime, Event_Time) as duration
from (select RS.session_id, RS.Event_Time as StartTime,
             (select min(event_time) 
              from amp_event_mamlaka em
              where em.session_id = RS.session_id and
                    em.Event_Type = 'Round End' and 
                    em.event_time > RS.Event_Time
             ) as EndTime
      from amp_event_mamlaka RS
     ) RS