select g.aab121 ,g.pkcount ,j.yycount ,j.zysum from
(select f.aab121,count(*) as pkcount from
(select e.aab121 from ac01 a join ab55 b on a.aab001 = b.bae601
join ab57 d on d.aab201 = b.aab201
join ab58 e on e.aab120 = d.aab120
join ac02 k on a.aac001 = k.aac001 where k.aac031 in('1','2') and a.xnh005='06') f
group by f.aab121) g join
(select count(*) as yycount,sum(c.akc264) as zysum,e.aab121
from ac01 a join ab55 b on a.aab001 = b.bae601
join kc38 c on a.aac001 = c.aac001
join ab57 d on d.aab201 = b.aab201
join ab58 e on e.aab120 = d.aab120
join ac02 k on a.aac001 = k.aac001
where k.aac031 in('1','2') and a.xnh005='06'
and c.aae040>=?||'00:00:00'
and c.aae040<=?||'00:00:00'
group by e.aab121) j on g.aab121 = j.aab121
uj5u.com熱心網友回復：
執行計劃貼出來看看uj5u.com熱心網友回復：
1、貼執行計劃；2、說下各表資料量，起碼哪些資料量比較大說下。
uj5u.com熱心網友回復：
好的,多謝各位了,祝端午節快樂.等假期過去附上執行計劃,在幫看看.uj5u.com熱心網友回復：
SQL優化如下：with tmp as
(
select e.aab121,c.akc264,c.aae040
from ac01 a
join ab55 b on a.aab001 = b.bae601
join ab57 d on d.aab201 = b.aab201
join ab58 e on e.aab120 = d.aab120
join ac02 k on a.aac001 = k.aac001
where k.aac031 in ('1','2')
and a.xnh005 = '06'
)
select aab121,
count(*) as pkcount,
sum(case when aae040 >=?||'00:00:00' and c.aae040<=?||'00:00:00' then 1 else 0 end) as yycount,
sum(case when aae040 >=?||'00:00:00' and c.aae040<=?||'00:00:00' then akc264 else 0 end) as zysum
from tmp
group by aab121
uj5u.com熱心網友回復：
表名和欄位名有些隨意，看著就頭痛啊。
uj5u.com熱心網友回復：
十分感謝,給我提供了一個新的思路，學到了，希望您萬事順心。uj5u.com熱心網友回復：
上次說好給各位附上我的sql執行計劃,請大家參考參考,再次真誠希望大家作業順利.uj5u.com熱心網友回復：
嗯,是的。要好好改進改進
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/10015.html
標籤：開發
上一篇：oracle子查詢語法求教