Oracle中获取连续的序列号范围的SQL (二)

方案3的思路比较值得学习，我们将层层剖析它的结构：

1：对每一条记录找它的前一行

所谓连续的序列号就是看相同格式的情况下前一行记录与当前行的记录序号的值是否相差为1：

• 如果相差为1，则说明两个序列号是连续的；
• 如果不为1，则两个序列号不是连续的

select numFormat,
lag(code, 1) over(partition by numFormat order by code) previousCode, code,
max(code) over(partition by numFormat) maxn
from inventory；

 

 2：过滤掉中间连续序列号。

这里通过比较当前序列号与前一行的序列号相减是非相差为1，相差为1将被过滤掉；相差不为1比如2或者说有null值则不被过滤。

select * from(

select numFormat,
lag(code, 1) over(partition by numFormat order by code) previousCode, code,
max(code) over(partition by numFormat) maxn
from inventory

)
where nvl(code-previousCode-1,1) <> 0
;

 

 3：把当前行的code列作为 startNum,后一行的previouscode列作为endNum

select numFormat,code startNum, lead(previousCode) over(partition BY numFormat order by previousCode ) endNum from(

select numFormat,
lag(code, 1) over(partition by numFormat order by code) previousCode, code,
max(code) over(partition by numFormat) maxn
from inventory

)
where nvl(code-previousCode-1,1) <> 0
;

 

 

但是我们这里却得到了错误的结果。

这里面有两个问题:

• 使用lead函数往后找的过程中，如果当前行已经是最后一行，则应该使用最大数字作为endNum，所以需要使用nvl函数来判断是否为null。
• 本该是第一行的的记录跑startNum为1的居然放到了最后一行。经过调查，previousCode的值存在为null的情况，oracle通常遇到为null值是把null放在最后，所以我们需要添加previousCode为null的行要排在前面。使用nulls first.

4：修复第3步的问题。

select numFormat,code startNum, nvl(lead(previousCode) over(partition BY numFormat order by previousCode nulls first ),maxn) endNum from(

select numFormat,
lag(code, 1) over(partition by numFormat order by code) previousCode, code,
max(code) over(partition by numFormat) maxn
from inventory

)
where nvl(code-previousCode-1,1) <> 0
;

 

注意红色部分的修改，这样就可以得到如下正确的结果。