sq优化?

Question

select lrfc.ziliao_id,
       count(*) as allCount,
       lycc.allCcCount,
       lycc.notPassCount,
       lycc.passCount,
       lycc2. currentCount,
       lycc2.timeNpCount
  from ly_release_for_cc lrfc
  left outer join (select ziliao_id,
                          count(*) as allCcCount,
                          sum(decode(status, 20, 1, null)) as notPassCount,
                          sum(decode(90, 1, null)) as passCount
                     from ly_cc
                    group by ziliao_id) lycc on lrfc.ziliao_id =
                                                lycc.ziliao_id
  left outer join (select ziliao_id,
                          count(*) as currentCount,
                          sum(decode(status, 20, 1, null)) as timeNpCount
                   
                     from ly_cc
                    where ly_cc.create_ly_cc_time >=
                          to_date('2009-02-11', 'yyyy-mm-dd') --加上时间过滤
                      and ly_cc.create_ly_cc_time <=
                          to_date('2009-02-12', 'yyyy-mm-dd')
                    group by ziliao_id) lycc2 on lrfc.ziliao_id =
                                                 lycc2.ziliao_id
 group by lrfc.ziliao_id,
          allCcCount,
          lycc.notPassCount,
          lycc.passCount,
          lycc2. currentCount,
          lycc2.timeNpCount上面sql怎么优化？？还可以少写少一个连接吗和效率上怎么优化
 还有一个语法问题 :left outer join (select ziliao_id,
                          count(*) as currentCount,
                          sum(decode(status, 20, 1, null)) as timeNpCount
                   
                     from ly_cc
                    where ly_cc.create_ly_cc_time >=
                          to_date('2009-02-11', 'yyyy-mm-dd') --加上时间过滤
                      and ly_cc.create_ly_cc_time <=
                          to_date('2009-02-12', 'yyyy-mm-dd')
                    group by ziliao_id) lycc2 on lrfc.ziliao_id =  --这里的lycc2好像必需的，我试试了，
                                                 lycc2.ziliao_id就是视图后的别名是必需的吗

Answer 1

我看是没有什么可优化了,两个子查询一个没有条件限制,一个条件限制我觉得主表ly_release_for_cc是不是要加一些where条件,然后去掉group by的分组,你看看这样做能不能达到你的要求?

Answer 2

sum(decode(status, 20, 1, null))
一般用sum(decode(..., 1, 0))或者count(decode(..., 1, null))sum(decode(90, 1, null)) 写错了吧两个join可以合并：时间过滤条件可以用case解决，和decode一样

Answer 3

select lrfc.ziliao_id,
       count(*) as allCount,
       lycc.allCcCount,
       lycc.notPassCount,
       lycc.passCount,
       lycc. currentCount,
       lycc.timeNpCount
  from ly_release_for_cc lrfc    
          ( select ziliao_id,
                  count(*) allccount,
                  sum(decode(status, 20, 1, null)) as notPassCount,
                  sum(decode(status, 90, 1, null)) as passCount,
                  sum(case
                        when create_ly_cc_time >=to_date('2009-02-11', 'yyyy-mm-dd') and
                             create_ly_cc_time <=to_date('2009-02-12', 'yyyy-mm-dd') then
                         1 else 0 end) currentCount,
                  sum(case
                        when create_ly_cc_time >=to_date('2009-02-11', 'yyyy-mm-dd') and
                             create_ly_cc_time <=to_date('2009-02-12', 'yyyy-mm-dd') and status = 20 then
                         1 else 0 end)
             from ly_cc group by ziliao_id ) lycc
   where lrfc.ziliao_id =lycc.ziliao_id(+)
   group by lrfc.ziliao_id,
          allCcCount,
          lycc.notPassCount,
          lycc.passCount,
          lycc. currentCount,
          lycc.timeNpCount从语法上可以这样改

Answer 4

还有个问题
还有一个语法问题 :select * 
from a left outer join (select ziliao_id, 
                          count(*) as currentCount, 
                          sum(decode(status, 20, 1, null)) as timeNpCount 
                  
                    from ly_cc 
                    where ly_cc.create_ly_cc_time >= 
                          to_date('2009-02-11', 'yyyy-mm-dd') --加上时间过滤 
                      and ly_cc.create_ly_cc_time <= 
                          to_date('2009-02-12', 'yyyy-mm-dd') 
                    group by ziliao_id) lycc2 on a.ziliao_id =  --这里的lycc2好像必需的，我试试了， 
                                                lycc2.ziliao_id 一直会用，但不知所以然 也就是外连接查询 ，这时原lycc2可以用表名ly_cc 代替吗(不考滤上面也用了ly_cc,就单独针对这段代码)

Answer 5

lycc和lycc2可以合并
时间过滤条件可以和sum(decode(status, 20, 1, null)) as timeNpCount合并，用case...when

Answer 6

别名当然要了，不然where和select怎么处理