mysql - 根据状态选择 1 条记录并计算持续时间并循环 SQL

Question

我有一个表，其中包含以下示例记录。

Id | tutorid | status | timestamp
1  | 21      | online | '2017-01-01 12:30:20'
2  | 21      | away   | '2017-01-01 13:27:20'
3  | 31      | online | '2017-01-01 14:38:20'
4  | 21      | offline| '2017-01-01 15:30:20'
5  | 31      | away   | '2017-01-01 17:20:20'
6  | 21      | online | '2017-01-01 18:30:20'
7  | 31      | online | '2017-01-01 18:30:20'
8  | 31      | away   | '2017-01-01 19:30:20'
9  | 21      | online | '2017-01-01 19:47:20'
10 | 31      | offline| '2017-01-01 19:59:20'
11 | 21      | offline| '2017-01-01 20:01:20'

让我知道如何为每个教程选择从“在线”到“离线”的时间戳。在线是登录和存在的时间(其中状态会比离线状态更早)，离线是注销时间。离开是导师的内部状态。

输出应该类似于

Tutorid | Online               | Offline
21      |'2017-01-01 12:30:20' | '2017-01-01 15:30:20'
31      |'2017-01-01 14:38:20' | '2017-01-01 19:59:20'
21      |'2017-01-01 18:30:20' | '2017-01-01 20:01:20'

Answer 1

这可能不完整，但这是一个开始......

SELECT tutorid
     , MIN(n.timestamp) online
     , y_timestamp offline 
  FROM 
     ( SELECT x.*
            , MIN(y.timestamp) y_timestamp 
         FROM my_table x 
         JOIN my_table y 
           ON y.tutorid = x.tutorid 
          AND y.timestamp > x.timestamp 
          AND y.status = 'offline' 
        WHERE x.status = 'online'  
        GROUP 
           BY tutorid
            , id
     ) n 
 GROUP
    BY tutorid
     , y_timestamp 
 ORDER 
    BY online;