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 )