mysql - 自连接从事件表中提取相关记录？

Question

我有一个包含以下字段的表

Time                 |     Status
2016-01-21 16:11:00  |     Connected
2016-01-21 16:51:00  |     Disconnected
2016-01-21 17:01:00  |     Connected
2016-01-21 17:10:00  |     Status X
2016-01-21 17:20:00  |     Status Y
2016-01-21 17:25:00  |     Disconnected
2016-01-21 17:30:00  |     Connected
2016-01-21 17:32:00  |     Disconnected

我希望输出是这样的

Disconnected                     Connected
2016-01-21 16:51:00     |         2016-01-21 17:01:00
2016-01-21 17:25:00     |         2016-01-21 17:30:00
2016-01-21 17:32:00     |

等等。也就是说，我想显示服务器断开连接时间的数据。

我写过这样的查询

select B.Time, A.Time
from ( select Time
       from table
       where Status = 'Connected') as A, table B
where B.Status = 'Disconnected'
and B.Time < A.Time;

我的输出是

Disconnected                     Connected
2016-01-21 16:51:00              2016-01-21 17:01:00
2016-01-21 16:51:00              2016-01-21 17:30:00     

也就是说，对于每个断开连接的事件，我得到的所有连接事件都大于它。但是，我只希望每行的第一个连接事件大于断开连接的事件。我能做什么？

Answer 1

一个好的方法是创建一个包含所有时间逻辑的更复杂的 ON 子句。 ( http://sqlfiddle.com/#!9/81f61/5/0 )

select dis.Time as disconnected, con.Time as connected
  from ev dis
  join ev con  ON con.Status = 'connected' 
              AND dis.Status = 'disconnected'
              AND con.Time = (select MIN(Time) 
                                 from ev
                                 where Status = 'connected'
                                   and Time > dis.Time)

这将每个连接行限制为仅考虑第一个 MIN() 连接时间晚于每个断开连接时间。

如果您有大量此类事件记录，请考虑在(Status, Time) 上放置索引以优化此查询。