mysql 查询缺失连续数据_Mysql数据连续范围查询与缺失范围查询

表结构与数据

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)

(责任编辑：最模板)