有一个序列表 seq
,它有一个存整数序列值的字段叫作 id,原本序列的值是连续递增的,但因某些原因,有的值丢失了,我们希望能通过 SQL 找出缺失值的范围。
先来构造有缺失值的 seq
表,可以用 SQL 派生出这个表。
WITH seq AS(
SELECT 1 AS id
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 15
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
)
我们观察数据可知,seq
表中目前最大的数是 20,缺失的值有:4、9、10、11、14、16、17。这些缺失值的范围是:
start | stop |
---|---|
4 | 4 |
9 | 11 |
14 | 14 |
16 | 17 |
上表就是我们需要通过 SQL 生成的结果。
接下来说说实现 SQL 的思路。
第一,把 seq 表中 id 字段的每个值 + 1 后再和 seq
表中的数比较,如果不在 seq
表中,说明该数 + 1 是缺失值,且是一段缺失值的范围的起始值。
SELECT
s.id + 1 AS START
FROM
seq AS s
LEFT JOIN seq AS r
ON s.id = r.id - 1
WHERE r.id IS NULL
查出来的结果如下,21 不应该出现,后面我们会加条件过滤掉。
START
--------
4
9
14
16
21
第二,在找到所有缺失数据的范围的起始值后,再从 seq
表中找到大于起始值的最小值 upper,upper - 1 就是该段缺失范围的结束值。比如对于缺失值 9,在 seq
表中能找到大于 9 的最小值是 12,12 - 1 = 11 就是该段缺失数据的范围的结束值。
SELECT
(SELECT
MIN(id) - 1
FROM
seq AS xx
WHERE xx.id > s.id) AS STOP
FROM
seq AS s
LEFT JOIN seq AS r
ON s.id = r.id - 1
WHERE r.id IS NULL
下面是找到的结束值,因为 21 原本不存在,所以它的结束值是 NULL。
STOP
--------
4
11
14
17
(NULL)
最后,加上过滤条件,完整的 SQL 如下:
SELECT
START,
STOP
FROM
(SELECT
s.id + 1 AS START,
(SELECT
MIN(id) - 1
FROM
seq AS xx
WHERE xx.id > s.id) AS STOP
FROM
seq AS s
LEFT JOIN seq AS r
ON s.id = r.id - 1
WHERE r.id IS NULL) AS t
WHERE STOP IS NOT NULL