这是差距和孤岛的问题。可以解决如下：
select t.status, t.from_time, coalesce(CAST(lead(from_time) over (partition by id_device order by from_time) AS varchar(20)), 'NOW') to_date, t.id_devicefrom( select t.status, min(checking_time) from_time, t.id_device from ( select *, row_number() over (partition by id_device, status order by checking_time) - row_number() over (partition by id_device order by checking_time) grn from data ) t group by t.id_device, grn, t.status) torder by t.id_device, t.from_time
dbffile演示
关键是最嵌套的子查询，在该子查询中，我使用两个
row_number函数来隔离设备上相同状态的连续出现。一旦有了
grn价值，剩下的就很容易了。
结果
status from_timeto_time id_device------------------------------------------------------------OK 2017-01-01 00:00:00 2017-01-01 00:01:00 1Failed 2017-01-01 00:01:00 2017-01-01 00:04:00 1OK 2017-01-01 00:04:00 NOW 1OK 2017-01-01 00:00:00 NOW 2OK 2017-01-01 00:00:00 NOW 3
