oracle 三

总结下今天SQL REVIEW时遇见的问题。主要还是使用了没有必要的子查询，使sql过于复杂。

 

详细请见下面两个的sql语句的改写。

 

selectcount(1) from

                      (select t.resource_link as url,

                     t.resource_id as resourceId,

                     t.resource_title as pinpaiName,

                     t.custom_type as activityType,

                     t.isnew as isNewActivity,

                     max(decode(t.expand_key,'activityLink',t.expand_value)) as activityLink,

                     max(decode(t.expand_key,'showLever',t.expand_value))as showLever,

                     max(decode(t.expand_key,'startTime',t.expand_value))as startTime,

                     max(decode(t.expand_key,'endTime',t.expand_value))as endTime,

                     max(decode(t.expand_key,'activityPicture',t.expand_value)) as activityPicture

                     from (select cms_props.resource_id,

                            cms_props.resource_link,

                             cms_props.resource_title,

                             cms_props.custom_type,

                             cms_props.isnew,

                             cms_expandx.expand_key,

                             cms_expandx.expand_value

                        from cms_online_resource_props cms_props ,

                      cms_online_resource_expand cms_expandx

                    where cms_expandx.expand_value isnotnull

                  and cms_expandx.resource_id = cms_props.resource_id

                   and (cms_props.custom_type = '1'or cms_props.custom_type = '2'or cms_props.custom_type = '3')

                  and cms_expandx.expand_key in

                      ('activityLink', 'showLever', 'startTime', 'endTime','activityPicture')

                  and cms_props.resource_path like

                             '/sites/pingan.com/huodong/peizhi/%.shtml'

                    and cms_props.resource_type = '28007' ) t

               groupbyt.resource_id,t.resource_link,t.resource_title,t.custom_type,t.isnew)d orderby d.startTime desc ;

 

 

可以改成下面语句

 

selectcount(*)

  fromcms_online_resource_props cms_props

where (cms_props.custom_type = '1'or cms_props.custom_type = '2'or

       cms_props.custom_type= '3')     

   and cms_props.resource_path like

       '/sites/pingan.com/huodong/peizhi/%.shtml'

   andcms_props.resource_type = '28007'

   andexists (select0

         from cms_online_resource_expand cms_expandx

        where cms_expandx.expand_value isnotnull  

           and cms_expandx.expand_key in

              ('activityLink', 'showLever', 'startTime',

               'endTime', 'activityPicture')

          and cms_expandx.resource_id = cms_props.resource_id

          and cms_expandx.expand_key in

              ('activityLink', 'showLever', 'startTime',

               'endTime', 'activityPicture')) ;

 

 

 

 

select * from (selectrownum rdd, content.* from (

      select d.* from

                      (select t.resource_link as url,

                     t.resource_id as resourceId,

                     t.resource_title as pinpaiName,

                     t.custom_type as activityType,

                     t.isnew as isNewActivity,

                     max(decode(t.expand_key,'activityLink',t.expand_value)) as activityLink,

                     max(decode(t.expand_key,'showLever',t.expand_value))as showLever,

                     max(decode(t.expand_key,'startTime',t.expand_value))as startTime,

                     max(decode(t.expand_key,'endTime',t.expand_value))as endTime,

                     max(decode(t.expand_key,'activityPicture',t.expand_value)) as activityPicture

                     from (select cms_props.resource_id,

                             cms_props.resource_link,

                             cms_props.resource_title,

                             cms_props.custom_type,

                             cms_props.isnew,

                             cms_expandx.expand_key,

                             cms_expandx.expand_value

                        from cms_online_resource_props cms_props ,

                      cms_online_resource_expand cms_expandx

                    where cms_expandx.expand_value isnotnull

                  and cms_expandx.resource_id = cms_props.resource_id

                  and (cms_props.custom_type = '1'or cms_props.custom_type = '2'or cms_props.custom_type = '3')

                   and cms_expandx.expand_key in

                      ('activityLink', 'showLever', 'startTime', 'endTime','activityPicture')

                  and cms_props.resource_path like

                             '/sites/pingan.com/huodong/peizhi/%.shtml'

                    and cms_props.resource_type = '28007' ) t

               groupbyt.resource_id,t.resource_link,t.resource_title,t.custom_type,t.isnew)d

      orderby  showLever,d.startTime desc) content

      WHERE

      rownum <= 3

       )WHERE

       rdd>1

 

可以改成下面语句

 

select *

  from (selectrownum rdd, d.*

         from (select cms_props.resource_link as url,

                      cms_props.resource_id as resourceId,

                      cms_props.resource_title as pinpaiName,

                      cms_props.custom_type as activityType,

                      cms_props.isnew as isNewActivity,

                      max(decode(cms_expandx.expand_key,

                                 'activityLink',

                                 cms_expandx.expand_value)) as activityLink,

                      max(decode(cms_expandx.expand_key,

                                 'showLever',

                                 cms_expandx.expand_value)) as showLever,

                      max(decode(cms_expandx.expand_key,

                                 'startTime',

                                  cms_expandx.expand_value))as startTime,

                      max(decode(cms_expandx.expand_key,

                                 'endTime',

                                 cms_expandx.expand_value)) as endTime,

                      max(decode(cms_expandx.expand_key,

                                 'activityPicture',

                                 cms_expandx.expand_value)) as activityPicture

                 from cms_online_resource_props  cms_props,

                      cms_online_resource_expandcms_expandx

                where cms_expandx.expand_value isnotnull

                  and cms_expandx.resource_id = cms_props.resource_id

                  and (cms_props.custom_type = '1'or

                      cms_props.custom_type = '2'or

                      cms_props.custom_type = '3')

                  and cms_expandx.expand_key in

                      ('activityLink', 'showLever', 'startTime', 'endTime',

                       'activityPicture')

                   and cms_props.resource_path like

                      '/sites/pingan.com/huodong/peizhi/%.shtml'

                  and cms_props.resource_type = '28007'

                groupby cms_props.resource_id,

                         cms_props.resource_link,

                         cms_props.resource_title,

                         cms_props.custom_type,

                         cms_props.isnew

                orderby showLever, startTime desc) d

        WHERErownum <= 3)

WHERE rdd > 1;