用SQL語句求排除斷號的號碼串

　　一用戶對繳款日報中的票據號使用情況提出要求希望以類似的方式展現以便直觀地反映實際使用的票據號情況

　　我們經過分析發現實現這一需求的難點主要有兩點

　　． 如果要找出斷號用SQL語句實現主要是要考慮性能

　　． 將排除斷後的使用號碼段的多條記錄轉換為一行顯示即用SQL實現行列轉換

　　如果通過編程來實現這兩點都不難但通過SQL來實現則需要一些技巧

　　假設知道已用票據號為最小為最大為求斷號的SQL如下

　　Select Rownum + ( )

　　From Dual

　　Connect By Rownum <= ( )

　　Minus

　　Select Column_Value Txt From Table(Cast(Zltoolsf_Numlist() As Zltoolst_Numlist))

　　求出的結果是三條記錄

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

　　求轉換為一行顯示的已用票據段的SQL如下

　　With TEST As(

　　Select Column_Value 編號 From Table(Cast(Zltoolsf_Numlist() As Zltoolst_Numlist))

　　)

　　Select Substr(Max(LPAD(Length(分段) ) || 分段) ) As 分段

　　From (

　　Select Sys_Connect_By_Path(分段 ) As 分段

　　From (

　　Select Rownum As 行號A起始號||||(B中斷號) As 分段

　　From (

　　Select Rownum As 行號編號 As 起始號

　　From (

　　Select 編號 From TEST

　　Minus

　　Select 編號+ From TEST)

　　) A

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

　　Select 編號+ As 編號 From TEST

　　Minus

　　Select 編號 From TEST)

　　) B

　　Where A行號=B行號)

　　Start With 行號 =

　　Connect By (行號) = Prior 行號)

　　查詢結果

　　其中用到以下技巧

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

　　． 用Sys_Connect_By_Path函數和樹型查詢實現多行記錄轉換為一列

　　． 用SubstrMaxLPADLength幾個函數的組合來求最長的一條記錄

　　如是Oracle G及以後的版本可以使用一個新的函數WmsysWm_Concat比前面樹型查詢的速度要快很多

　　With TEST As(

　　Select Column_Value 編號 From Table(Cast(Zltoolsf_Numlist() As Zltoolst_Numlist))

　　)

　　Select WmsysWm_Concat(分段) as 分段

　　From (

　　Select Rownum As 行號A起始號||||(B中斷號) As 分段

　　From (

　　Select Rownum As 行號編號 As 起始號

　　From (

　　Select 編號 From TEST

　　Minus

　　Select 編號+ From TEST)

　　) A

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

　　Select 編號+ As 編號 From TEST

　　Minus

　　Select 編號 From TEST)

　　) B

　　Where A行號=B行號)

From:http://tw.wingwit.com/Article/program/Oracle/201311/17815.html