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