日期:2014-05-17  浏览次数:20713 次

麻烦看看此SQL能如何优化。
需求:
显示可销天数,7天平均数量,30天平均数量,季度平均数量的显示。
问题:
此SQL中有张单表数据达到2400W。(需要优化.)
SQL,此SQL还未完结.现在只显示了可销天数,7天销量.剩下的字段还未显示.(但实现的方法和7天比较类似)

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (select a.mc, a.spm, a.xssl, b.xssl1, a.kcsl
                  from (select tb.name mc, ta.spm, avg(ta.xssl) xssl, ta.kcsl
                          from CC_JXC_GYS_DAY ta,
                               (select * from inf_shop where fgs = 'C') tb
                         where ta.gys = '10035'
                           and ta.mc = tb.code
                           and ta.rq < add_months(sysdate, -1)
                         group by tb.name, ta.spm, ta.kcsl
                         order by kcsl desc) a,
                       (select tb.name mc,
                               ta.spm,
                               avg(ta.xssl) xssl1,
                               ta.kcsl kcsl
                          from CC_JXC_GYS_DAY ta,
                               (select * from inf_shop where fgs = 'C') tb
                         where ta.gys = '10035'
                           and ta.mc = tb.code
                           and to_char(ta