一用戶對繳款日報中的票據(jù)號使用情況提出要求，希望以類似5-6,9-10,12-20的方式展現(xiàn)，以便直觀地反映實際使用的票據(jù)號情況。

　　我們經(jīng)過分析發(fā)現(xiàn)，實現(xiàn)這一需求的難點主要有兩點：

　　1. 如果要找出斷號，用SQL語句實現(xiàn)，主要是要考慮性能;

　　2. 將排除斷后的使用號碼段的多條記錄轉(zhuǎn)換為一行顯示，即用SQL實現(xiàn)行列轉(zhuǎn)換;

　　如果通過編程來實現(xiàn)，這兩點都不難，但通過SQL來實現(xiàn)，則需要一些技巧。

　　假設(shè)知道已用票據(jù)號為3,4,5,7,8,11,12，最小為3，最大為12，求斷號的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

　　其中用到一個技巧就是用Connect by Rownum來產(chǎn)生按順序增長的記錄集。

　　求轉(zhuǎn)換為一行顯示的已用票據(jù)段的SQL如下：

 

With TEST As(
Select Column_Value 編 號 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 行號,A.起始號||'-'||(B.中斷號 -1) As 分段
From (
Select Rownum As 行號,編號 As 起始號
From (
Select 編號 From TEST
Minus
Select 編號+1 From TEST)
) A,
(Select Rownum As 行號,編號 As 中斷號 From (
Select 編號+1 As 編 號 From TEST
Minus
Select 編號 From TEST)
) B
Where A.行號=B.行號)
Start With 行號 = 1
Connect By (行號 -1) = Prior 行號)

　查詢結(jié)果： 3-5,7-8,11-12

　　其中用到以下技巧：

　　1. 用minus方式求已用號碼段的起始號和終止號的記錄集

　　2. 用Sys_Connect_By_Path函數(shù)和樹型查詢實現(xiàn)多行記錄轉(zhuǎn)換為一列

　　3. 用Substr,Max,LPAD,Length幾個函數(shù)的組合來求最長的一條記錄

　　如是Oracle 10G及以后的版本，可以使用一個新的函數(shù)Wmsys.Wm_Concat，比前面樹型查詢的速度要快很多。

 

　　 With TEST As(

　　Select Column_Value 編 號 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 行號,A.起始號||'-'||(B.中斷號-1) As 分段

　　From (

　　 Select Rownum As 行號,編號 As 起始號

　　From (

　　Select 編號 From TEST

　 　Minus

　　Select 編號+1 From TEST)

　　) A,

　　(Select Rownum As 行 號,編號 As 中斷號 From (

　　Select 編號+1 As 編號 From TEST

　　Minus

　　 Select 編號 From TEST)

　　) B

　　Where A.行號=B.行號)