MySQL obtains the continuous range. mysql obtains the range.

MySQL obtains the continuous range. mysql obtains the 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. Refer to MySQL technology Insider: SQL programming 2.6.2 continuous range problem