oracle 如何聚合多行函數
在BEA論壇上看一位專家寫的大作,一條SQL語句是
select r.xm,
substr(r.csrq,1,4)||'年'||substr(r.csrq,5,2)||'月'||substr(r.csrq,7,2)||'日' csrq,
(select dictvalue from zh_dictvalue where dictcode=xb and dictname='rk_xb') xb,
(select dictvalue from zh_dictvalue where dictcode=mz and dictname='rk_mz') mz,
(select dictvalue from zh_dictvalue where dictcode=ssxq and dictname='rk_xzqh') ssxq,
xz,
xp,
xz,
fwcs
from czrk_jbxx r,rk_zpxx p
where r.gmsfhm=p.gmsfhm and rownum<2
(select dictvalue from zh_dictvalue where dictcode=xb and dictname='rk_xb') xb, (select dictvalue from zh_dictvalue where dictcode=mz and dictname='rk_mz') mz, (select dictvalue from zh_dictvalue where dictcode=ssxq and dictname='rk_xzqh') ssxq, 這里如何優化?
也就是符合條件的多條記錄要合并成一條記錄的多個字段.
其實之前有好多這樣的問題,但沒有一個好的方案,都是嵌套太多,性能損失很大,把三條記錄的結果合并.如果最后的sql語句中的select超過三次,那真的還不如直接這樣查詢.
不過首先這個方法是錯誤的,因為這三次都在原表中查詢,性能損失很大,其實如果是5條,10條,20條,100條.這樣的語句寫起來就累死人了.
之前有人提供了幾個方案,但都是連成字符串還不是形成多列.真正形成多列應該是用分析函數:
這樣實際上只對原表做一次查詢,然后得到的結果集在顯示的時候被提前到一行上形成多列.
select * from (
select name,
lead(name,1) over (order by name) as name1,
lead(name,2) over (order by name) as name2,
lead(name,3) over (order by name) as name3,
lead(name,4) over (order by name) as name4
from tb_customer where 條件
) t
where t.name4 is not null
這樣原來的行數越多節省的性能越高,因為實際原表查詢只有一次,后來只是對內存中的結果做合并.上面的那個例子就是
select * from (
select dictvalue as mz,
lead(dictvalue,1) over (order by dictcode) as ssxq,
lead(dictvalue,2) over (order by dictcode) as xb,
from zh_dictvalue
where (dictcode=xb and dictname='rk_xb')
or (dictcode=mz and dictname='rk_mz')
or (dictcode=ssxq and dictname='rk_xzqh')
) t
where t.ssxq not null
不知道有沒有發現
select dictvalue as mz,
lead(dictvalue,1) over (order by dictcode) as ssxq,
lead(dictvalue,2) over (order by dictcode) as xb,
后面as的順序?
因為dictvalue值未知,如果按它排序,出來的值并不和mz,ssxq,xm有順序對應的關系,所以以dictcode排序,那么出來的值就是上面的對應關系.