MySQL 获取不连续的值，mysql获取

MySQL 获取不连续的值，mysql获取

1、问题

有一个表数据如下图显示。下图数据中，running_number应该是连续性的5位字符。现需要获取到连接断掉的running_number。如00001与00005间缺了00002,00003,00004。我们需要获取到这三个running_number。

2、解决方案

2.1 获得缺号前的临界点值。

select running_number,time  from 
(select running_number,time from device_data  where device_id=13 
order by running_number asc) t
where not exists (select 1 from (select running_number from device_data  where device_id=13 ) b 
where b.running_number+0=t.running_number-1 )

2.2 根据临界点来获取缺号