mysql - 根据条件计算事件的总时间

Question

我有一个下表

CREATE TABLE log (  
     id INT NOT NULL AUTO_INCREMENT,  
     keyNo VARCHAR(16),  
     date DATE NOT NULL,
     time TIME NOT NULL,
     t1Event VARCHAR(255),
     t2Event VARCHAR(255),  
     PRIMARY KEY  (id)  
)

我按以下方式有表格行

1 205 2018-07-10 12:01 Lclosed keyout
2 205 2018-07-10 12:02 Lclosed keyout
3 205 2018-07-10 12:03 Lclosed keyinside
4 205 2018-07-10 12:44 Lclosed keyout
5 205 2018-07-10 13:02 Lclosed keyinside

执行以下查询后得到的表

Select * from reportsuser where keyNo = '205' and date = '2018-07-10' and t1Event='Lclosed'

不，我想计算 key 拔出的总时间，可以通过第一个 keyout 计算出来事件和第一keyinside在第一个之后keyout事件。像这样，我想通过总结所有这些时间差来计算总时间，例如在上面的情况下我想要 (3 - 1) + (5 - 4)就sno而言。我怎样才能实现这个mysql查询呢？由于此类查询的时间段可以是月或年，最好的方法是什么？

Answer 1

CREATE TABLE log (  
     id INT NOT NULL AUTO_INCREMENT,  
     dt DATETIME NOT NULL,
     status VARCHAR(255),  
     PRIMARY KEY  (id)  
);

INSERT INTO log VALUES
(1, '2018-07-10 12:01:00' ,'keyout'),
(2, '2018-07-10 12:02:00' ,'keyout'),
(3, '2018-07-10 12:03:00' ,'keyinside'),
(4, '2018-07-10 12:44:00' ,'keyout'),
(5, '2018-07-10 13:02:00' ,'keyinside');


SELECT MIN(dt) dto 
     , dti
     , SEC_TO_TIME(TIME_TO_SEC(dti)-TIME_TO_SEC(MIN(dt))) diff
  FROM 
     (  SELECT x.dt
             , MIN(y.dt) dti
          FROM log x
          JOIN log y ON y.dt > x.dt 
           AND y.status = 'keyinside'
         WHERE x.status = 'keyout'
         GROUP 
            BY x.dt
     ) a
 GROUP
    BY dti;

    +---------------------+---------------------+----------+
    | dto                 | dti                 | diff     |
    +---------------------+---------------------+----------+
    | 2018-07-10 12:01:00 | 2018-07-10 12:03:00 | 00:02:00 |
    | 2018-07-10 12:44:00 | 2018-07-10 13:02:00 | 00:18:00 |
    +---------------------+---------------------+----------+