select bfcboaid,count(bfid ) ,
SUM(case when RACHARGETYPE IN ('SS', 'CP', 'YY')
and (RARECJE is not null or RARECJE <> 0) then 1 else 0 end )现金应收总笔数,
sum (case when RACHARGETYPE IN ('SS', 'CP', 'YY')
and (RARECJE is not null or RARECJE <> 0) then RARECJE else 0 end ) 现金应收总金额,
SUM(case when RACHARGETYPE IN ('SS', 'CP', 'YY') then 1 else 0 end )现金回收总笔数,
sum (case when RACHARGETYPE IN ('SS', 'CP', 'YY') then RARECJE else 0 end ) 现金应收总金额,
SUM(case when (RARECJE is not null or RARECJE <> 0) then 1 else 0 end ) 欠收笔数,
sum (case when (RARECJE is not null or RARECJE <> 0) then RARECJE else 0 end ) 欠收金额
from recaccount,bookframe_1
where RAREADDATE > to_date('2010-10-1', 'YYYY-MM-DD')
and RAMBFID=bfid and bftype='DB'
group by BFCBOAIDrecaccount从10月1号到现在的数据大概有10W条,这语句要查4分钟左右才能查出数据,如何优化?
SUM(case when RACHARGETYPE IN ('SS', 'CP', 'YY')
and (RARECJE is not null or RARECJE <> 0) then 1 else 0 end )现金应收总笔数,
sum (case when RACHARGETYPE IN ('SS', 'CP', 'YY')
and (RARECJE is not null or RARECJE <> 0) then RARECJE else 0 end ) 现金应收总金额,
SUM(case when RACHARGETYPE IN ('SS', 'CP', 'YY') then 1 else 0 end )现金回收总笔数,
sum (case when RACHARGETYPE IN ('SS', 'CP', 'YY') then RARECJE else 0 end ) 现金应收总金额,
SUM(case when (RARECJE is not null or RARECJE <> 0) then 1 else 0 end ) 欠收笔数,
sum (case when (RARECJE is not null or RARECJE <> 0) then RARECJE else 0 end ) 欠收金额
from recaccount,bookframe_1
where RAREADDATE > to_date('2010-10-1', 'YYYY-MM-DD')
and RAMBFID=bfid and bftype='DB'
group by BFCBOAIDrecaccount从10月1号到现在的数据大概有10W条,这语句要查4分钟左右才能查出数据,如何优化?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货