求高手,做个优化,t1,t2,各100W记录,t ,50条,t4,1W
select t.comp_id,
decode(tt4.persum,null,0,tt4.persum) count,
nvl(tt1.count,0)+nvl(tt2.count,0) alltimes,
tt3.count wfcount
from t
left join (select comp_id,count(1) persum from t4 group by comp_id) tt4 on tt4.comp_id=t.comp_id
left join (select crop_num,count(1) count from t1 group by crop_num) tt1 on tt1.crop_num=t.comp_id
left join (select crop_num,count(1) count from t2 group by crop_num) tt2 on tt2.crop_num=t.comp_id
left join (select crop_num,count(1) count from t2 where t2.wfxw like '%违反%' group by crop_num) tt3 on tt3.crop_num=t.comp_id
select t.comp_id,
decode(tt4.persum,null,0,tt4.persum) count,
nvl(tt1.count,0)+nvl(tt2.count,0) alltimes,
tt3.count wfcount
from t
left join (select comp_id,count(1) persum from t4 group by comp_id) tt4 on tt4.comp_id=t.comp_id
left join (select crop_num,count(1) count from t1 group by crop_num) tt1 on tt1.crop_num=t.comp_id
left join (select crop_num,count(1) count from t2 group by crop_num) tt2 on tt2.crop_num=t.comp_id
left join (select crop_num,count(1) count from t2 where t2.wfxw like '%违反%' group by crop_num) tt3 on tt3.crop_num=t.comp_id
decode(tt4.persum,null,0,tt4.persum) count,
nvl(tt1.count,0)+nvl(tt2.count,0) alltimes,
tt3.count wfcount
from t
left join (select comp_id,count(1) persum from t4 where t4.comp_id in (select comp_id from t) group by comp_id) tt4 on tt4.comp_id=t.comp_id
left join (select crop_num,count(1) count from t1 where t1.comp_num in (select comp_id from t) group by crop_num) tt1 on tt1.crop_num=t.comp_id
left join (select crop_num,count(1) count from t2 where t2.comp_num in (select comp_id from t) and t2.wfxw like '%违反%' group by crop_num) tt3 on tt3.crop_num=t.comp_id