表结构与数据

CREATE TABLE `kaysen_ranges` (
  `number` int(10) unsigned NOT NULL,
  PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into `kaysen_ranges` (`number`) values('1');
insert into `kaysen_ranges` (`number`) values('2');
insert into `kaysen_ranges` (`number`) values('3');
insert into `kaysen_ranges` (`number`) values('100');
insert into `kaysen_ranges` (`number`) values('101');
insert into `kaysen_ranges` (`number`) values('103');
insert into `kaysen_ranges` (`number`) values('104');
insert into `kaysen_ranges` (`number`) values('105');

查询连续范围过程

目的：

首先将行号计算出来，根据连续的行号反映出连续范围的规律

mysql> SELECT @n:=@n+1 AS line, number FROM kaysen_ranges, (SELECT @n:=0) AS n;
+------+--------+
| line | number |
+------+--------+
|    1 |      1 |
|    2 |      2 |
|    3 |      3 |
|    4 |    100 |
|    5 |    101 |
|    6 |    103 |
|    7 |    104 |
|    8 |    105 |
+------+--------+
8 rows in set (0.00 sec)

在同一组连续值内，连续数值差不变的表示没有间断，当出现一个新组时，其列和行号之间的差值开始增大

mysql> SELECT line, number, number-line AS diff
    -> FROM
    -> (
    ->     SELECT @n:=@n+1 AS line, number FROM kaysen_ranges, (SELECT @n:=0) AS n
    -> ) AS diff_tab;
+------+--------+------+
| line | number | diff |
+------+--------+------+
|    1 |      1 |    0 |
|    2 |      2 |    0 |
|    3 |      3 |    0 |
|    4 |    100 |   96 |
|    5 |    101 |   96 |
|    6 |    103 |   97 |
|    7 |    104 |   97 |
|    8 |    105 |   97 |
+------+--------+------+
8 rows in set (0.00 sec)

所以对于连续范围的统计，我们可以根据差值进行分组统计：

mysql> SELECT MIN(number) AS start_range, MAX(number) AS end_range
    -> FROM
    -> (
    ->     SELECT line, number, number-line AS diff
    ->     FROM
    ->     (
    ->         SELECT @n:=@n+1 AS line, number FROM kaysen_ranges, (SELECT @n:=0) AS n
    ->     ) AS diff_tab
    ->
    -> ) AS range_tab
    -> GROUP BY diff;
+-------------+-----------+
| start_range | end_range |
+-------------+-----------+
|           1 |         3 |
|         100 |       101 |
|         103 |       105 |
+-------------+-----------+
3 rows in set (0.00 sec)

简写：

SELECT MIN(number) AS start_range, MAX(number) AS end_range
FROM
(
    SELECT @n:=@n+1 AS line, number FROM kaysen_ranges, (SELECT @n:=0) AS n
) AS diff_tab
GROUP BY number-line

查询数据的缺失范围，即非连续范围的过程

目的：

首先对数据进行位移匹配，如果是连续的值，那么其差值应该在1，反之大于1

mysql> SELECT number, (SELECT MIN(number) FROM kaysen_ranges AS tmp_tab WHERE tmp_tab.number>range_tab.number) AS next_num
    -> FROM kaysen_ranges AS range_tab;
+--------+----------+
| number | next_num |
+--------+----------+
|      1 |        2 |
|      2 |        3 |
|      3 |      100 |
|    100 |      101 |
|    101 |      103 |
|    103 |      104 |
|    104 |      105 |
|    105 |     NULL |
+--------+----------+
8 rows in set (0.00 sec)

根据结果我们可以知道，next_num-number=1时，值为连续的，不连续的值为(3, 100),(101, 103)
正确的应该是(4, 99), (102, 102)，即(number+1,next_num-1)

mysql> SELECT number+1 AS start_range, next_num-1 AS end_range
    -> FROM
    -> (
    ->     SELECT number, (SELECT MIN(number) FROM kaysen_ranges AS tmp_tab WHERE tmp_tab.number>range_tab.number) AS next_num
    ->     FROM kaysen_ranges AS range_tab
    -> ) AS deficiency_tab
    -> WHERE next_num-number>1;
+-------------+-----------+
| start_range | end_range |
+-------------+-----------+
|           4 |        99 |
|         102 |       102 |
+-------------+-----------+
2 rows in set (0.00 sec)