oracle 如何聚合多行函數(shù)
在BEA論壇上看一位專(zhuān)家寫(xiě)的大作,一條SQL語(yǔ)句是
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, 這里如何優(yōu)化?
也就是符合條件的多條記錄要合并成一條記錄的多個(gè)字段.
其實(shí)之前有好多這樣的問(wèn)題,但沒(méi)有一個(gè)好的方案,都是嵌套太多,性能損失很大,把三條記錄的結(jié)果合并.如果最后的sql語(yǔ)句中的select超過(guò)三次,那真的還不如直接這樣查詢(xún).
不過(guò)首先這個(gè)方法是錯(cuò)誤的,因?yàn)檫@三次都在原表中查詢(xún),性能損失很大,其實(shí)如果是5條,10條,20條,100條.這樣的語(yǔ)句寫(xiě)起來(lái)就累死人了.
之前有人提供了幾個(gè)方案,但都是連成字符串還不是形成多列.真正形成多列應(yīng)該是用分析函數(shù):
這樣實(shí)際上只對(duì)原表做一次查詢(xún),然后得到的結(jié)果集在顯示的時(shí)候被提前到一行上形成多列.
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
這樣原來(lái)的行數(shù)越多節(jié)省的性能越高,因?yàn)閷?shí)際原表查詢(xún)只有一次,后來(lái)只是對(duì)內(nèi)存中的結(jié)果做合并.上面的那個(gè)例子就是
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
不知道有沒(méi)有發(fā)現(xiàn)
select dictvalue as mz,
lead(dictvalue,1) over (order by dictcode) as ssxq,
lead(dictvalue,2) over (order by dictcode) as xb,
后面as的順序?
因?yàn)閐ictvalue值未知,如果按它排序,出來(lái)的值并不和mz,ssxq,xm有順序?qū)?yīng)的關(guān)系,所以以dictcode排序,那么出來(lái)的值就是上面的對(duì)應(yīng)關(guān)系.