一用戶(hù)對(duì)繳款日?qǐng)?bào)中的票據(jù)號(hào)使用情況提出要求，希望以類(lèi)似5-6,9-10,12-20的方式展現(xiàn)，以便直觀地反映實(shí)際使用的票據(jù)號(hào)情況。
我們經(jīng)過(guò)分析發(fā)現(xiàn)，實(shí)現(xiàn)這一需求的難點(diǎn)主要有兩點(diǎn)：
1. 如果要找出斷號(hào)，用SQL語(yǔ)句實(shí)現(xiàn)，主要是要考慮性能;
2. 將排除斷后的使用號(hào)碼段的多條記錄轉(zhuǎn)換為一行顯示，即用SQL實(shí)現(xiàn)行列轉(zhuǎn)換;
如果通過(guò)編程來(lái)實(shí)現(xiàn)，這兩點(diǎn)都不難，但通過(guò)SQL來(lái)實(shí)現(xiàn)，則需要一些技巧。
假設(shè)知道已用票據(jù)號(hào)為3,4,5,7,8,11,12，最小為3，最大為12，求斷號(hào)的SQL如下：
Select Rownum + (3 - 1) From Dual Connect By Rownum <= 12 - (3 - 1) Minus Select Column_Value Txt From Table(Cast(Zltools.f_Num2list('3,4,5,7,8,11,12') As Zltools.t_Numlist)) |
求出的結(jié)果是三條記錄，6,9,10
其中用到一個(gè)技巧就是用Connect by Rownum來(lái)產(chǎn)生按順序增長(zhǎng)的記錄集。
求轉(zhuǎn)換為一行顯示的已用票據(jù)段的SQL如下：
With TEST As(
Select Column_Value 編 號(hào) From Table(Cast(Zltools.f_Num2list('3,4,5,7,8,11,12') As Zltools.t_Numlist))
)
Select Substr(Max(LPAD(Length(分段), 5,'0') || 分 段), 7, 1000) As 分段
From (
Select Sys_Connect_By_Path(分 段, ',') As 分段
From (
Select Rownum As 行號(hào),A.起始號(hào)||'-'||(B.中斷號(hào) -1) As 分段
From (
Select Rownum As 行號(hào),編號(hào) As 起始號(hào)
From (
Select 編號(hào) From TEST
Minus
Select 編號(hào)+1 From TEST)
) A,
(Select Rownum As 行號(hào),編號(hào) As 中斷號(hào) From (
Select 編號(hào)+1 As 編 號(hào) From TEST
Minus
Select 編號(hào) From TEST)
) B
Where A.行號(hào)=B.行號(hào))
Start With 行號(hào) = 1
Connect By (行號(hào) -1) = Prior 行號(hào))
查詢(xún)結(jié)果： 3-5,7-8,11-12
其中用到以下技巧：
1. 用minus方式求已用號(hào)碼段的起始號(hào)和終止號(hào)的記錄集
2. 用Sys_Connect_By_Path函數(shù)和樹(shù)型查詢(xún)實(shí)現(xiàn)多行記錄轉(zhuǎn)換為一列
3. 用Substr,Max,LPAD,Length幾個(gè)函數(shù)的組合來(lái)求最長(zhǎng)的一條記錄
如是Oracle 10G及以后的版本，可以使用一個(gè)新的函數(shù)Wmsys.Wm_Concat，比前面樹(shù)型查詢(xún)的速度要快很多。
With TEST As(
Select Column_Value 編 號(hào) From Table(Cast(Zltools.f_Num2list('3,4,5,7,8,11,12') As Zltools.t_Numlist))
)
Select Wmsys.Wm_Concat(分段) as 分段
From (
Select Rownum As 行號(hào),A.起始號(hào)||'-'||(B.中斷號(hào)-1) As 分段
From (
Select Rownum As 行號(hào),編號(hào) As 起始號(hào)
From (
Select 編號(hào) From TEST
Minus
Select 編號(hào)+1 From TEST)
) A,
(Select Rownum As 行 號(hào),編號(hào) As 中斷號(hào) From (
Select 編號(hào)+1 As 編號(hào) From TEST
Minus
Select 編號(hào) From TEST)
) B
Where A.行號(hào)=B.行號(hào))
