本文介绍了如何在 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 中查找数字序列中的间隙的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!