有关sql的优化

Question

select * from (
select sum(jyje) total_je,xh,xm,xbdm,bjmc,yxmc,zymc from
(
select t.xh,t.xm,t.xbdm ,m.jyrq,m.jyje,t.bjmc,t.yxmc,t.zymc
from v_xs_bzksjbxx t,v_ykt_tjxx m where t.xh=m.xh and to_char(jyrq,'yyyy-MM')<='2009-08' and to_char(jyrq,'yyyy-MM')>='2008-09'
and 1=1 group by (t.xh,t.xm,t.xbdm ,m.jyrq,m.jyje,t.bjmc,t.yxmc,t.zymc)
) group by (xh,xm,xbdm,bjmc,yxmc,zymc)
) where total_je >200
这个语句查询的数据不是很多，但是v_ykt_tjxx中数据至少有26万多数据，并且一直会增加，如何优化这个sql语句，还望大家给点意见，很急。

Answer 1

你的SQL可以简化，不需要两层group by
[code="sql"]select *
from (--select sum(jyje) total_je, xh, xm, xbdm, bjmc, yxmc, zymc
--from (
select t.xh,
t.xm,
t.xbdm,
--m.jyrq,
sum(m.jyje) total_je,
t.bjmc,
t.yxmc,
t.zymc
from v_xs_bzksjbxx t, v_ykt_tjxx m
where t.xh = m.xh
and to_char(jyrq, 'yyyy-MM') <= '2009-08'
and to_char(jyrq, 'yyyy-MM') >= '2008-09'
and 1 = 1
group by (t.xh, t.xm, t.xbdm, --m.jyrq, m.jyje,
t.bjmc, t.yxmc, t.zymc)--)
--group by (xh, xm, xbdm, bjmc, yxmc, zymc)
)
where total_je > 200[/code]