熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 16:06:26  來源: Oracle 

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

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

  . 如果要找出斷號用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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.