mysql - 按顺序从两行中选择数据作为单行

Question

enter image description here如下图所示，相同的vehicle_id有6条记录(不同日期有3条IN，3条OUT)。我需要的结果为:

ID vehicle_id        IN               OUT
 1    X         first_record        second_record
 2    x         third_record        fourth_record
 3    x         fifth_record        sixth_record

因此，对于一个记录，一个 IN 时间和一个 OUT 时间。

是否可以使用选择查询来获取，或者我是否需要编写存储过程？

Answer 1

例如，您可以使用带有限制子句的子查询

drop table if exists t;

create table t(id int auto_increment primary key, vid int, trip_status varchar(3),dt datetime);

insert into t (vid,trip_status,dt)
values
(1,'in','2018-12-01 01:00:00'),
(1,'out','2018-12-01 02:00:00'),
(1,'in','2018-12-01 03:00:00'),
(1,'out','2018-12-01 04:00:00'),
(1,'in','2018-12-01 05:00:00'),
(1,'in','2018-12-01 05:00:00');

select t.* 
        , (select case when t1.trip_status ='out' then trip_status
                    else concat(t1.trip_status, '**Error**')
                    end
                from t t1 where t1.vid = t.vid and t1.id > t.id  order by t1.id limit 1) nexttrip_status
        , (select t1.dt from t t1 where t1.vid = t.vid and t1.id > t.id  order by t1.id limit 1) next_dt
from t where trip_status = 'in';

+----+------+-------------+---------------------+-----------------+---------------------+
| id | vid  | trip_status | dt                  | nexttrip_status | next_dt             |
+----+------+-------------+---------------------+-----------------+---------------------+
|  1 |    1 | in          | 2018-12-01 01:00:00 | out             | 2018-12-01 02:00:00 |
|  3 |    1 | in          | 2018-12-01 03:00:00 | out             | 2018-12-01 04:00:00 |
|  5 |    1 | in          | 2018-12-01 05:00:00 | in**Error**     | 2018-12-01 05:00:00 |
|  6 |    1 | in          | 2018-12-01 05:00:00 | NULL            | NULL                |
+----+------+-------------+---------------------+-----------------+---------------------+
4 rows in set (0.00 sec)