用SQL语句求排除断号的号码串

一用户对缴款日报中的票据号使用情况提出要求，希望以类似5-6,9-10,12-20的方式展现，以便直观地反映实际使用的票据号情况。

　　我们经过分析发现，实现这一需求的难点主要有两点：

　　1． 如果要找出断号，用SQL语句实现，主要是要考虑性能；

　　2． 将排除断后的使用号码段的多条记录转换为一行显示，即用SQL实现行列转换；

　　如果通过编程来实现，这两点都不难，但通过SQL来实现，则需要一些技巧。

　　假设知道已用票据号为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))

　　求出的结果是三条记录，6,9,10

　　其中用到一个技巧就是用Connect by Rownum来产生按顺序增长的记录集。

　　求转换为一行显示的已用票据段的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 行号)

　　查询结果： 3-5,7-8,11-12

　　其中用到以下技巧：

　　1． 用minus方式求已用号码段的起始号和终止号的记录集

　　2． 用Sys_Connect_By_Path函数和树型查询实现多行记录转换为一列

　　3． 用Substr,Max,LPAD,Length几个函数的组合来求最长的一条记录

　　如是Oracle 10G及以后的版本，可以使用一个新的函数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.行号)