我想在oracle中找到名为(s_no)的列和名为(test_table)的表的最小缺失数,然后编写以下代码。
select
min_s_no-1+level missing_number
from (
select min(s_no) min_s_no, max(s_no) max_s_no
from test_table
) connect by level <= max_s_no-min_s_no+1
minus
select s_no from test_table
;
结果,我得到了所有遗漏的号码。但我想选择最小
数。谁能帮我。
提前致谢。
最佳答案
使用解析函数LEAD
可以从下一行以升序获取数字。如果将此值与原始数字加1进行比较,则会得到缺失值(如果两个数字不匹配)。
要获得升序的第一个缺失值,与选择MIN
值相同:
select
num,
lead(num) over (order by num) num_lead,
case when num + 1 != lead(num) over (order by num) then num + 1 end as missing_num
from test_data
order by num;
NUM NUM_LEAD MISSING_NUM
---------- ---------- -----------
4 5
5 6
6 9 7
9 10
10 13 11
13
-- first missing number = MIN missing number
select min(missing_num)
from (
select
case when num + 1 != lead(num) over (order by num) then num + 1 end as missing_num
from test_data
);
MIN(MISSING_NUM)
----------------
7
ADDENDUM
编写SQL的一个好习惯是考虑边缘情况-这里是一个包含不带孔的完整间隔的表。第一个缺失值将是最后一个数字的后继。
select nvl(min(missing_num),max(num)+1) first_missing_value
from (
select
num,
case when num + 1 != lead(num) over (order by num) then num + 1 end as missing_num
from test_data
);
完整的表不返回
MISSING_NUM
,因此原始查询返回NULL
。使用NVL
提供了预期的结果。关于sql - 在oracle中查找最小缺失数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43155737/