select t3.*, nvl(t5.sum_kcsl, 0) sum_kcsl, nvl(t5.sum_clsl, 0) sum_clsl
from (select t2.fydm,
t2.mc,
t2.gg,
t2.kfbh,
t2.pch,
t2.sfkccl,
t2.dw,
t2.jx,
t2.dj,
sum(t2.sl) as zsl,
sum(t2.je) as zje
from zl_zydj t, fy_fymx t2
where t2.jzxh = t.jzxh
and t2.zhbh = t.zhbh
and t2.zxbz = '0'
and (t2.fylb = '0' or t2.wzlb = '1')
and t2.kfbh = '103116'
and t2.zhbh = '3305230028'
and t2.fyzt = '0'
and t2.jzlb = '1'
and t.zyzt <> '4'
group by t2.fydm,
t2.mc,
t2.gg,
t2.kfbh,
t2.pch,
t2.sfkccl,
t2.dw,
t2.jx,
t2.dj
) t3
left join (select t4.wzbh,
t4.kfbh,
t4.pch,
sum(t4.kcsl) as sum_kcsl,
sum(t4.clsl) as sum_clsl
from kc_pckctz t4
where 1 = 1
and t4.kfbh = '103116'
and t4.zxbz = '0'
and t4.sfty = '0'
and t4.zhbh = '3305230028'
and t4.yxqz >= '20170214'
and (t4.kcsl <> 0.0 or t4.clsl <> 0.0)
group by t4.wzbh, t4.kfbh, t4.pch) t5
on t3.pch = t5.pch
and t3.fydm = t5.wzbh
and t3.kfbh = t5.kfbh
and ((decode(t3.pch, null, 1, 0)) = 1 or
(t5.sum_kcsl <> 0 or t5.sum_clsl <> 0))
下面是執行計劃：
uj5u.com熱心網友回復：
1、zl_zydj 表是否有合適的索引列2、根據業務需要，適當的參考 sum over(partition by )，會比group by那么多列快
uj5u.com熱心網友回復：
and t2.zhbh = '3305230028' 改為?
and t.zhbh = '3305230028'
?
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103496.html
標籤：開發