MySQL obtains the continuous range
1. Raw Data
2. Convert running_number into data and add an ordered number to the column.
SELECT d.running_number+0 running_number,@a:=@a+1 rn FROM device_data d,(SELECT @a:=0) a where d.device_id=13 order by d.running_number;
3. The difference between running_number and ordered data. If the difference is the same as diff, running_number indicates that it is continuous.
SELECT running_number,rn,running_number-rn diffFROM (SELECT d.running_number+0 running_number,@a:=@a+1 rn FROM device_data d,(SELECT @a:=0) a where d.device_id=13) b;
4. grouping based on the difference and obtaining the minimum and maximum running_number of the correlation difference forms a continuous range.
select min(c.running_number) min,max(c.running_number) maxfrom(SELECT running_number,rn,running_number-rn diff FROM (SELECT d.running_number+0 running_number,@a:=@a+1 rn FROM device_data d,(SELECT @a:=0) a where d.device_id=13) b)c group by diff;
5. Reference MySQL technology Insider: SQL programming 2.6.2 continuous range problem http://book.2cto.com/201211/8313.html