问题遇到的现象和发生背景
每一个设备都有N个夹子,夹子可以拿出与放回,每一次拿出与放回都会新增一条数据,现在要将数据合并,得出使用的时长。
每一个设备都有N个夹子,夹子可以拿出与放回,每一次拿出与放回都会新增一条数据,现在要将数据合并,得出使用的时长。
这里主要的问题是需要一个序号,以便能每2行一组进行合并,如果是mysql8.0版本,则可以用下面的方式
---测试数据
create table test_clip_tmp0
(device_mac varchar(30),
clip_id varchar(50),
action_status tinyint,
storage_datetime datetime,
take_datetime datetime);
insert into test_clip_tmp0 values('A','A1',1,'2022-01-27 10:00:00',null);
insert into test_clip_tmp0 values('A','A1',0,null,'2022-01-27 10:00:03');
insert into test_clip_tmp0 values('A','A2',1,'2022-01-27 15:00:00',null);
insert into test_clip_tmp0 values('A','A2',0,null,'2022-01-27 15:00:03');
insert into test_clip_tmp0 values('B','A1',1,'2022-01-27 10:00:01',null);
insert into test_clip_tmp0 values('B','A1',0,null,'2022-01-27 10:00:02');
--查询sql
select device_mac,clip_id,g,max(storage_datetime) storage_datetime,max(take_datetime) take_datetime from (
SELECT T.*,cast((ROW_NUMBER() OVER(order by device_mac,clip_id,IFNULL(storage_datetime,take_datetime)))/2 as int) g
FROM test_clip_tmp0 T ORDER BY device_mac,clip_id,IFNULL(storage_datetime,take_datetime)) as tt
group by device_mac,clip_id,g