一用户对缴款日报中的票据号使用情况提出要求希望以类似的方式展现以便直观地反映实际使用的票据号情况
我们经过分析发现实现这一需求的难点主要有两点
． 如果要找出断号用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行号)
