select sum(nvl(a.ysje, 0) - nvl(a.skje, 0)) ye, a.custcode, a.unitcode
from (select decode(zb.djdl, 'ys', sum(fb.Jfbbje), '0') ysje,
decode(zb.djdl, 'sk', sum(fb.ybye), '0') skje,
bas.custcode,
corp.unitcode
from arap_djzb zb
left join arap_djfb fb
on zb.VOUCHID = fb.VOUCHID
left join bd_cumandoc man
on man.pk_cumandoc = fb.ksbm_cl
left join bd_cubasdoc bas
on bas.pk_cubasdoc = man.pk_cubasdoc
left join bd_corp corp
on corp.pk_corp = zb.dwbm
where bas.custcode = '211200127'
-- substr(bas.custcode, 0, 2) = '11'
and zb.djzt = '2'
and corp.unitcode = '10301'
and nvl(zb.dr, 0) = 0
--and zb.dr = 0
and nvl(fb.dr, 0) = 0
group by bas.custcode, zb.djdl, corp.unitcode) a
group by a.custcode, a.unitcode
關聯幾張表，查詢結果有一條資料，但是效率很低，要十幾秒，有沒有地方可以優化的大神們
uj5u.com熱心網友回復：
對于驅動表，給本地謂詞列建立索引。對于被驅動表，給關聯列建索引，同時也要考慮給被驅動表的本地謂詞列加入到索引中。
uj5u.com熱心網友回復：
bas.custcode = '211200127'and corp.unitcode = '10301'
寫在相應on的后面
uj5u.com熱心網友回復：
以下的基礎上，適當的創建索引。select sum(nvl(decode(zb.djdl, 'ys', sum(fb.Jfbbje), '0'), 0) - nvl(decode(zb.djdl, 'sk', sum(fb.ybye), '0'), 0)) ye,
bas.custcode,
corp.unitcode
from arap_djzb zb
left join arap_djfb fb
on zb.VOUCHID = fb.VOUCHID
left join bd_cumandoc man
on man.pk_cumandoc = fb.ksbm_cl
left join bd_cubasdoc bas
on bas.pk_cubasdoc = man.pk_cubasdoc
left join bd_corp corp
on corp.pk_corp = zb.dwbm
where bas.custcode = '211200127'
-- substr(bas.custcode, 0, 2) = '11'
and zb.djzt = '2'
and corp.unitcode = '10301'
and nvl(zb.dr, 0) = 0
--and zb.dr = 0
and nvl(fb.dr, 0) = 0
group by bas.custcode, zb.djdl, corp.unitcode ;
uj5u.com熱心網友回復：
1、沒有執行計劃和資料環境談SQL優化，沒什么依據，實際就是猜；
2、樓主說只有一條資料，這個是分組，也許這個組里資料量很多。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/31408.html
標籤：開發