如何在 SQL Server 中查找数字序列中的间隙 [英] How to find gaps in a sequence of numbers in SQL Server

问题描述

我正在尝试在此表中找到最小的缺失数字.

I am trying to find the smallest missing number in this table.

+------+--------+
| id   | number |
+------+--------+
|  902 |    1   |
|  908 |    2   |
| 1007 |    7   |
| 1189 |    8   |
| 1233 |   12   |
| 1757 |   15   |
+------+--------+

在数字列中，您可以看到数字之间存在多个间隙.我需要得到最小间隙后的数字.所以在上面的例子中，我需要数字 3.因为 2 是间隔后的最小数字.

In the number column, you can see there are several gaps between the numbers. I need to get the number after the smallest gap. So in the case above I need the number 3. Because 2 is the smallest number after a gap.

推荐答案

我会使用 lead():

select min(id) + 1
from (select t.*,
             lead(id) over (order by id) as next_id
      from t
     ) t
where next_id <> id + 1;

如果您想确保 id 从 1 开始(因此如果缺少1"，则返回)，您可以这样做:

If you want to ensure that the ids start at 1 (so if "1" is missing, then return that), you can do:

select (case when min(minid) <> 1 then 1 else min(id) + 1 end)
from (select t.*, min(id) over () as minid
             lead(id) over (order by id) as next_id
      from t
     ) t
where next_id <> id + 1;

这篇关于如何在 SQL Server 中查找数字序列中的间隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！