项目中的sql语句

# 以下sql可能性能不是很好，但用到了很多sql知识(备忘)：

1. insert..select 批量导入数据。

2. SPP_BOOKING_SEQ.nextval 序列的使用。

3. group by & order by 二者同时使用时，order by后的排序字段需要出现在select中。

4. (+)所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配。

5. nvl去空函数。

6. sum, min, group by 使用多个聚合函数。

 

sql1:

 

insert into pexweb.spp_supp_search_result_stg
  (spp_kw_cat_id,
   spp_kw_cat_desc,
   spp_kw_cat_type,
   last_1_mth_search_cnt,
   last_3_mth_search_cnt,
   last_12_mth_search_cnt,
   refresh_date)

  select SPP_SUPP_SEARCH_RESULT_SEQ.nextval,
         spp_keyword,
         'keyword',
         last_1_mth_search_cnt,
         last_3_mth_search_cnt,
         last_12_mth_search_cnt,
         sysdate
    from (select *
            from (select distinct (c.spp_keyword),
                                  'keyword',
                                  nvl(a.last_1_mth_search_cnt, 0) last_1_mth_search_cnt,
                                  nvl(b.last_3_mth_search_cnt, 0) last_3_mth_search_cnt,
                                  c.last_12_mth_search_cnt,
                                  c.earliest_search_mth,
                                  sysdate
                    from (select spp_keyword,
                                 sum(search_cnt) last_1_mth_search_cnt
                            from spp_keyword_search_detail
                           where search_mth between
                                 (select to_number(to_char(sysdate - interval '1'
                                                           month,
                                                           'yyyymm'))
                                    from dual) and
                                 (select to_number(to_char(sysdate, 'yyyymm'))
                                    from dual)
                           group by spp_keyword) a,
                           
                         (select spp_keyword,
                                 sum(search_cnt) last_3_mth_search_cnt
                            from spp_keyword_search_detail
                           where search_mth between
                                 (select to_number(to_char(sysdate - interval '3'
                                                           month,
                                                           'yyyymm'))
                                    from dual) and
                                 (select to_number(to_char(sysdate, 'yyyymm'))
                                    from dual)
                           group by spp_keyword) b,
                           
                         (select spp_keyword,
                                 sum(search_cnt) last_12_mth_search_cnt,
                                 min(search_mth) earliest_search_mth
                            from spp_keyword_search_detail
                           where search_mth between
                                 (select to_number(to_char(sysdate - interval '12'
                                                           month,
                                                           'yyyymm'))
                                    from dual) and
                                 (select to_number(to_char(sysdate, 'yyyymm'))
                                    from dual)
                           group by spp_keyword) c
                           
                   where a.spp_keyword(+) = c.spp_keyword
                     and b.spp_keyword(+) = c.spp_keyword) d
           order by last_12_mth_search_cnt desc, earliest_search_mth desc) 
    where rownum <= 200000

 

 sql2: 子查询

左连接：左边字段全部会出现

select a.product_id "productId",
       a.folder_id "folderId",
       a.model_number "modelNumber",
       a.short_desc "shortDesc",
       a.category_desc "categoryDesc",
       nvl2(b.id, 'ON', 'OFF') onlineStatus
  from (select f.*
          from folder_product f
         where f.folder_id in (6000000094184, 6000000326795)
           and (posting_status is null or
               posting_status in ('Online', 'Offline'))
           and model_number like '%nov%') a
           
  left join (select distinct id
               from product_val_entity p1
              where attr_name = 'OnlineProduct'
                and exists (select 1
                       from product_val_code
                      where attr_name = 'WebsiteType'
                        and value = 'GSOL'
                        and id = p1.value)) b on a.product_id = b.id

 

sql3: 公用的部分先准备好

 with temp_data as (
select id from product_val_entity pve
where 
exists (select 1
          from product_val_code
         where attr_name = 'Status'
           and value = 'ON'
           and id = pve.id)
   and exists (select 1
          from product_val_code
         where attr_name = 'WebsiteType'
           and value = 'GSOL'
           and id = pve.id)
   and exists (select 1                                 
                  from product_val_entity                       
                 where attr_name = 'Contract'                   
                   and value = '2508813011239'                  
                   and id = pve.id)
 )
 
select a.pdfSpecSheet, b.additionalProduct, c.additionalImages 
 from (select count(*) pdfSpecSheet
        from product_val_entity pve
       where attr_name = 'Supplier'
        and value = '2008808594470' 
        and exists (select 1 
                from product_val_attachment
		           where attr_name = 'SpecFile'
		            and id = pve.id)
        and exists (select 1 
                from temp_data 
                where id = pve.id)) a,
 
      (select count(*) additionalProduct                       
         from product_val_entity pve                           
        where attr_name = 'Supplier'                           
         and value = '2008808594470'                          
         and exists (select 1                                 
                  from product_val_group                        
                 where attr_name = 'ImageGroup'                 
                   and id = pve.id) 
           and exists (select 1 
                  from temp_data 
                 where id = pve.id)) b,
                   
       (select count(*) additionalImages                        
          from product_val_group pve                            
         where attr_name = 'ImageGroup'                           
           and exists (select 1                                 
                  from product_val_entity pve                   
                 where attr_name = 'Supplier'                   
                   and value = '2008814022300'                  
                   and id = pve.id) 
           and exists (select 1 
                  from temp_data 
                 where id = pve.id)) c