# 以下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
相关推荐
MyBatis Log Plug最新插件,适用于需要打印项目中SQL语句,原来插件市场的是免费的,现在需要收费了,不会安装的可以私聊我
经典SQL语句大全,包括java或者C#中各种增删改查的语句,经典实用。
淘淘项目sql语句,亲测可用,可以放心下载使用。资源分享
自己写的SQL语句 最近公司里面做报表就写了点 也许对大家有点点帮助。O(∩_∩)O谢谢
在项目开发的过程中难免需要打印一下自己拼写的SQL语句,尤其是很长的SQL语句,看起来很不舒服,使用该jar包,打印出来的SQL语句已经被格式化,结构很清晰、简洁,更方便我们调试及分析
小区物业管理系统配套数据库SQL语句,在Oracle中导入该SQL语句,数据库建立成功
通过解析sql语句获取表血缘关系
本文详细介绍了常用的SQL语句的用法以及实例!
项目中已使用的一个比较简单的SQL语句查询字段解析工具类
谷粒商城分布式大型项目SQL语句文件,项目笔记专栏地址:https://blog.csdn.net/weixin_38297695/category_10730234.html
sql语句生成的工具,可以通过该工具生成对应sql语句
net实例常用SQL语句大全, 例如:语 句 功 能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 --数据...
1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。 2、掌握使用SQL语句修改表的结构。 3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。 1.创建名为“Project”的数据库 2.在Project数据库下...
内涵两个项目,一个是EF写的,一个是用sql语句直接连接数据库写的。包含数据库脚本
适合根据视频自学的朋友做这个项目是需要的准备资料,里面包括美工做的网页以及sql语句等等
包含最常用的SQL语句,关连查询,分组,表分区等
SQL语句经典之作!SQL语句经典之作!SQL语句经典之作!SQL语句经典之作!SQL语句经典之作!SQL语句经典之作!SQL语句经典之作!SQL语句经典之作!SQL语句经典之作!
一个项目涉及到的50个Sql语句(整理版) 经典的SQL语句txt文件
OA项目源代码+sql语句
添加各省市的sql语句,可以将各省市添加到数据库中~~