SQL 确定序列里缺失值的范围
有一个序列表 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 本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-05-30，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读