時(shí)間：2015-06-28 00:00:00 來(lái)源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評(píng)論(0)
一用戶對(duì)繳款日?qǐng)?bào)中的票據(jù)號(hào)使用情況提出要求，希望以類似5-6,9-10,12-20的方式展現(xiàn)，以便直觀地反映實(shí)際使用的票據(jù)號(hào)情況。
我們經(jīng)過(guò)分析發(fā)現(xiàn)，實(shí)現(xiàn)這一需求的難點(diǎn)主要有兩點(diǎn)：
1． 如果要找出斷號(hào)，用SQL語(yǔ)句實(shí)現(xiàn)，主要是要考慮性能；
2． 將排除斷后的使用號(hào)碼段的多條記錄轉(zhuǎn)換為一行顯示，即用SQL實(shí)現(xiàn)行列轉(zhuǎn)換；
如果通過(guò)編程來(lái)實(shí)現(xiàn)，這兩點(diǎn)都不難，但通過(guò)SQL來(lái)實(shí)現(xiàn)，則需要一些技巧。
假設(shè)知道已用票據(jù)號(hào)為3,4,5,7,8,11,12，最小為3，最大為12，求斷號(hào)的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
其中用到一個(gè)技巧就是用Connect by Rownum來(lái)產(chǎn)生按順序增長(zhǎng)的記錄集。
求轉(zhuǎn)換為一行顯示的已用票據(jù)段的SQL如下：
With TEST As(
Select Column_Value 編號(hào) 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 行號(hào),A.起始號(hào)||'-'||(B.中斷號(hào)-1) As 分段
From (
Select Rownum As 行號(hào),編號(hào) As 起始號(hào)
From (
Select 編號(hào) From TEST
Minus
Select 編號(hào)+1 From TEST)
) A,
(Select Rownum As 行號(hào),編號(hào) As 中斷號(hào) From (
Select 編號(hào)+1 As 編號(hào) From TEST
Minus
Select 編號(hào) From TEST)
) B
Where A.行號(hào)=B.行號(hào))
Start With 行號(hào) = 1
Connect By (行號(hào)-1) = Prior 行號(hào))
查詢結(jié)果： 3-5,7-8,11-12
其中用到以下技巧：
1． 用minus方式求已用號(hào)碼段的起始號(hào)和終止號(hào)的記錄集
2． 用Sys_Connect_By_Path函數(shù)和樹(shù)型查詢實(shí)現(xiàn)多行記錄轉(zhuǎn)換為一列
3． 用Substr,Max,LPAD,Length幾個(gè)函數(shù)的組合來(lái)求最長(zhǎng)的一條記錄
如是Oracle 10G及以后的版本，可以使用一個(gè)新的函數(shù)Wmsys.Wm_Concat，比前面樹(shù)型查詢的速度要快很多。
With TEST As(
Select Column_Value 編號(hào) 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 行號(hào),A.起始號(hào)||'-'||(B.中斷號(hào)-1) As 分段
From (
Select Rownum As 行號(hào),編號(hào) As 起始號(hào)
From (
Select 編號(hào) From TEST
Minus
Select 編號(hào)+1 From TEST)
) A,
(Select Rownum As 行號(hào),編號(hào) As 中斷號(hào) From (
Select 編號(hào)+1 As 編號(hào) From TEST
Minus
Select 編號(hào) From TEST)
) B
Where A.行號(hào)=B.行號(hào))
關(guān)鍵詞標(biāo)簽：SQL語(yǔ)句
相關(guān)閱讀
熱門文章
Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法
oracle中使用SQL語(yǔ)句修改字段類型-oracle修改SQL語(yǔ)句案例
誤刪Oracle數(shù)據(jù)庫(kù)實(shí)例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語(yǔ)句修改字段類型-oracle修改SQL語(yǔ)句案例 Oracle中使用alter table來(lái)增加,刪除,修改列的語(yǔ)法 ORACLE SQL 判斷字符串是否為數(shù)字的語(yǔ)句 ORACLE和SQL語(yǔ)法區(qū)別歸納(1) oracle grant 授權(quán)語(yǔ)句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法