所以这是我有一个表的场景，它记录来自设备的 gps 数据以及引擎状态(打开或关闭)。我需要找出引擎状态开启和关闭的时间。

vehicle_id | engine_status | time_stamp
 1         |    on         |2014-05-21 07:30:02
 2         |    off        |2014-05-21 07:40:02
 1         |    on         |2014-05-21 07:50:02
 1         |    on         |2014-05-21 08:00:02
 1         |    on         |2014-05-21 08:10:02
 2         |    on         |2014-05-21 08:20:02
 1         |    off        |2014-05-21 08:30:02
 1         |    off        |2014-05-21 08:40:02
 2         |    on         |2014-05-21 08:50:02
 1         |    off        |2014-05-21 09:00:02
 2         |    on         |2014-05-21 09:10:02
 1         |    off        |2014-05-21 09:10:06
 1         |    off        |2014-05-21 09:30:02
 2         |    on         |2014-05-21 09:30:02
 1         |    off        |2014-05-21 09:35:02
 2         |    on         |2014-05-21 09:39:02
 1         |    off        |2014-05-21 09:40:02
 1         |    off        |2014-05-21 09:45:02
 2         |    off        |2014-05-21 09:50:02
 1         |    on         |2014-05-21 09:55:02
 1         |    on         |2014-05-21 09:56:02
 2         |    off        |2014-05-21 09:58:02
 1         |    on         |2014-05-21 09:59:02
 1         |    on         |2014-05-21 09:59:02
 1         |    on         |2014-05-21 10:10:02
 2         |    off        |2014-05-21 09:30:02

最佳答案

考虑以下...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (vehicle_id INT NOT NULL
 ,engine_status VARCHAR(12) NOT NULL
 ,time_stamp TIMESTAMP NOT NULL
 );

 INSERT INTO my_table VALUES
 (1,'on'         ,'2014-05-21 07:30:02'),
 (2,'off'        ,'2014-05-21 07:40:02'),
 (1,'on'         ,'2014-05-21 07:50:02'),
 (1,'on'         ,'2014-05-21 08:00:02'),
 (1,'on'         ,'2014-05-21 08:10:02'),
 (2,'on'         ,'2014-05-21 08:20:02'),
 (1,'off'        ,'2014-05-21 08:30:02'),
 (1,'off'        ,'2014-05-21 08:40:02'),
 (2,'on'         ,'2014-05-21 08:50:02'),
 (1,'off'        ,'2014-05-21 09:00:02'),
 (2,'on'         ,'2014-05-21 09:10:02'),
 (1,'off'        ,'2014-05-21 09:10:06'),
 (1,'off'        ,'2014-05-21 09:30:02'),
 (2,'on'         ,'2014-05-21 09:30:02'),
 (1,'off'        ,'2014-05-21 09:35:02'),
 (2,'on'         ,'2014-05-21 09:39:02'),
 (1,'off'        ,'2014-05-21 09:40:02'),
 (1,'off'        ,'2014-05-21 09:45:02'),
 (2,'off'        ,'2014-05-21 09:50:02'),
 (1,'on'         ,'2014-05-21 09:55:02'),
 (1,'on'         ,'2014-05-21 09:56:02'),
 (2,'off'        ,'2014-05-21 09:58:02'),
 (1,'on'         ,'2014-05-21 09:59:02'),
 (1,'on'         ,'2014-05-21 09:59:02'),
 (1,'on'         ,'2014-05-21 10:10:02'),
 (2,'off'        ,'2014-05-21 09:30:02');

 SELECT vehicle_id
      , engine_status
      , SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end,start)))) total
   FROM
      (
 SELECT a.vehicle_id
      , a.engine_status
      , a.time_stamp start
      , MIN(c.time_stamp) end 
   FROM 
      ( SELECT *
             , CASE WHEN @prev= vehicle_id THEN @i:=@i+1 ELSE @i:=1 END rank
             , @prev:=vehicle_id 
          FROM my_table
             , ( SELECT @i:=1,@prev:='')x 
         ORDER 
            BY vehicle_id
             , time_stamp
      ) a
   LEFT 
   JOIN 
      ( SELECT *
             , CASE WHEN @prev= vehicle_id THEN @i:=@i+1 ELSE @i:=1 END rank
             , @prev:=vehicle_id 
          FROM my_table
             , ( SELECT @i:=1,@prev:='')x 
         ORDER 
            BY vehicle_id
             , time_stamp
      ) b  
     ON b.vehicle_id = a.vehicle_id 
    AND b.engine_status = a.engine_status
    AND b.rank = a.rank - 1  
   LEFT 
   JOIN 
      ( SELECT *
             , CASE WHEN @prev= vehicle_id THEN @i:=@i+1 ELSE @i:=1 END rank
             , @prev:=vehicle_id 
          FROM my_table
             , ( SELECT @i:=1,@prev:='')x 
         ORDER 
            BY vehicle_id
             , time_stamp
      ) c 
     ON c.vehicle_id = a.vehicle_id
    AND c.engine_status = a.engine_status
    AND c.rank >= a.rank 
   LEFT 
   JOIN 
      ( SELECT *
             , CASE WHEN @prev= vehicle_id THEN @i:=@i+1 ELSE @i:=1 END rank
             , @prev:=vehicle_id 
          FROM my_table
             , ( SELECT @i:=1,@prev:='')x 
         ORDER 
            BY vehicle_id
             , time_stamp
      ) d 
     ON d.vehicle_id = c.vehicle_id  
    AND d.engine_status = c.engine_status
    AND d.rank = c.rank + 1 
  WHERE b.rank IS NULL 
    AND c.rank IS NOT NULL
    AND d.rank IS NULL
  GROUP 
     BY a.vehicle_id
      , a.engine_status
      , a.rank
      ) x
   GROUP BY vehicle_id, engine_status;
+------------+---------------+----------+
| vehicle_id | engine_status | total    |
+------------+---------------+----------+
|          1 | off           | 01:15:00 |
|          1 | on            | 00:55:00 |
|          2 | off           | 00:08:00 |
|          2 | on            | 01:10:00 |
+------------+---------------+----------+

关于mysql - 找出两个记录之间的时间，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/23777887/