這里有一個(gè)簡單的庫存表，code代表的是序列號(hào)，numformat代表的是序列號(hào)的格式。每一條記錄代表的是一條庫存記錄。
??現(xiàn)在有這樣的一個(gè)需求，把連續(xù)的序列號(hào)給范圍給找出來。期望結(jié)果如下：
應(yīng)該怎樣來實(shí)現(xiàn)該需求？
方案1：
將整個(gè)表的數(shù)據(jù)載入到內(nèi)存中，通過循環(huán)遍歷，找到FormatA和FormatB的起始結(jié)束序列號(hào)。少量庫存記錄是沒有問題的。但是如果庫存記錄達(dá)到了上千萬條，顯然容易出現(xiàn)內(nèi)存溢出或者運(yùn)行緩慢的問題。
方案2：
通過SQL將相同的Format格式的序列號(hào)按照順序合并到一條記錄。
Oracle 提供了XMLAGG函數(shù)可以將序列號(hào)按照一定的順序拼接到一起，返回的類型是CLOB。LISTAGG 函數(shù)也提供類似的功能，但最大字符長度是4000。庫存表序列號(hào)拼接到一起往往超過4000個(gè)字符，所以在這里選擇的是XMLAGG函數(shù)。具體可以參考：https://blog.csdn.net/zqkwcyx/article/details/88663982
提供如下的SQL例子：
with inventory as(
select 'FormatA' as numFormat, 0001 as code from dual union
select 'FormatA' as numFormat, 0002 as code from dual union
select 'FormatA' as numFormat, 0003 as code from dual union
select 'FormatA' as numFormat, 0005 as code from dual union
select 'FormatA' as numFormat, 0006 as code from dual union
select 'FormatA' as numFormat, 0008 as code from dual union
select 'FormatB' as numFormat, 0001 as code from dual union
select 'FormatB' as numFormat, 0002 as code from dual union
select 'FormatB' as numFormat, 0005 as code from dual union
select 'FormatB' as numFormat, 0006 as code from dual union
select 'FormatB' as numFormat, 0008 as code from dual union
select 'FormatB' as numFormat, 0009 as code from dual
)
select numformat,rtrim(xmlagg(XMLELEMENT(e,orig.code,',').EXTRACT('//text()') order by orig.code).GetClobVal(),',') NumberClob from (
SELECT * FROM inventory
) orig
group by orig.numFormat
;
?然后也是通過循環(huán)遍歷，找到FormatA和FormatB的起始結(jié)束序列號(hào)。
比較方案1，查詢出的記錄數(shù)要少得多，不會(huì)出現(xiàn)方案1的問題。缺點(diǎn)是依然需要載入到內(nèi)存中，進(jìn)行循環(huán)遍歷，對(duì)CPU損耗比較大。
方案3：
能否避免在內(nèi)存中遍歷，直接使用SQL找到序列號(hào)的范圍呢？答案是有的，參考lag,lead函數(shù)。
https://blog.csdn.net/weixin_41287692/article/details/80577828
with inventory as(
select 'FormatA' as numFormat, 0001 as code from dual union
select 'FormatA' as numFormat, 0002 as code from dual union
select 'FormatA' as numFormat, 0003 as code from dual union
select 'FormatA' as numFormat, 0005 as code from dual union
select 'FormatA' as numFormat, 0006 as code from dual union
select 'FormatA' as numFormat, 0008 as code from dual union
select 'FormatB' as numFormat, 0001 as code from dual union
select 'FormatB' as numFormat, 0002 as code from dual union
select 'FormatB' as numFormat, 0005 as code from dual union
select 'FormatB' as numFormat, 0006 as code from dual union
select 'FormatB' as numFormat, 0008 as code from dual union
select 'FormatB' as numFormat, 0009 as code from dual
)
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
;
注意SQL里面使用了 nulls first: 字段包含了null值，如果不使用nulls first 該條記錄會(huì)放在最后最終導(dǎo)致范圍查找出錯(cuò)。
細(xì)節(jié)可以參考：?https://blog.51cto.com/hbxztc/1891420
