Oracle Database 11g SQL 开发指南学习笔记：高级查询
来源：互联网 发布：淘宝运营计划书怎么写 编辑：程序博客网 时间：2024/04/30 12:55
1、集合操作
--1.intersectwith tas( select 1 as v,'abc' as vv from dual union all select 1 ,'abc' from dual union all select 2,'def' from dual),ttas( select 1 as v,'abc' as vv from dual union all select 1,'abc' from dual union all select 3,'def' from dual)/*只返回一条记录，说明求交集后，会去重。从执行计划中看，会首先会对第一个表有一个sort操作，选项是unique，也就是去重。然后第二个表，也是一样。也就是先把每个表的记录进行排序去重，然后再求交集 V VV---------- --- 1 abc */select v,vv from tintersect select v,vv from tt--2.minuswith tas( select 1 as v,'abc' as vv from dual union all select 1 ,'abc' from dual union all select 2,'def' from dual),ttas( select 1 as v,'abc' as vv from dual union all select 3,'def' from dual)/*从执行计划中看，会首先会对第一个表有一个sort操作，选项是unique，也就是去重。然后第二个表，也是一样。然后第一个结果集减去第二个结果集，所以只会返回一条记录。 V VV---------- --- 2 def */select v,vv from t minusselect v,vv from tt
2、decode函数、translate函数
select v, --decode函数类似于case when，可有多个参数 decode(v, 1,1, 2,2, 3,3 ), --translate函数类似于replace，不过是加强版，按照替换规则进行替换 translate(vv, --要替换的字符串 'abcdefghi', --被替换的字符 '123456789') --替换为的字符 from ( select 1 as v,'abc' as vv from dual union all select 2 as v,'def' as vv from dual union all select 3 as v,'ghi' as vv from dual)
3、层次化查询
--1.层次查询：自顶向下、自底向上--自顶向下with tas( select 1 as v,null parent_v,'01' vv from dual union all select 2 as v,1,'02' as vv from dual union all select 3 as v,2,'03' as vv from dual union all select 4 as v,2,'04' as vv from dual union all select 5 as v,1,'05' as vv from dual union all select 6 as v,5,'06' as vv from dual union all select 7 as v,6,'07' as vv from dual union all select 8 as v,5,'08' as vv from dual union all select 9 as v,8,'09' as vv from dual union all select 10 as v,1,'10' as vv from dual union all select 11 as v,10,'11' as vv from dual union all select 12 as v,10,'12' as vv from dual union all select 13 as v,10,'13' as vv from dual)/*LPAD('',2*LEVEL-1)||VV ------------------------------------ 01 02 03 04 05 06 07 08 09 10 11 12 13 */select lpad(' ',2 * level - 1) || vvfrom tstart with v = 1connect by prior v = parent_v;--自底向上with tas( select 1 as v,null parent_v,'01' vv from dual union all select 2 as v,1,'02' as vv from dual union all select 3 as v,2,'03' as vv from dual union all select 4 as v,2,'04' as vv from dual union all select 5 as v,1,'05' as vv from dual union all select 6 as v,5,'06' as vv from dual union all select 7 as v,6,'07' as vv from dual union all select 8 as v,5,'08' as vv from dual union all select 9 as v,8,'09' as vv from dual union all select 10 as v,1,'10' as vv from dual union all select 11 as v,10,'11' as vv from dual union all select 12 as v,10,'12' as vv from dual union all select 13 as v,10,'13' as vv from dual)/*LPAD('',2*LEVEL-1)||VV ----------------------------------------------------------------09 08 05 01 */--注意，level是伪劣，第一层恢复返回1select lpad(' ',2 * level - 1) || vvfrom tstart with v = 9connect by v = prior parent_v; --connect by prior parent_v = v 效果是一样的--2.过滤--通过start with的条件，从非根结点开始遍历with tas( select 1 as v,null parent_v,'01' vv from dual union all select 2 as v,1,'02' as vv from dual union all select 3 as v,2,'03' as vv from dual union all select 4 as v,2,'04' as vv from dual union all select 5 as v,1,'05' as vv from dual union all select 6 as v,5,'06' as vv from dual union all select 7 as v,6,'07' as vv from dual union all select 8 as v,5,'08' as vv from dual union all select 9 as v,8,'09' as vv from dual union all select 10 as v,1,'10' as vv from dual union all select 11 as v,10,'11' as vv from dual union all select 12 as v,10,'12' as vv from dual union all select 13 as v,10,'13' as vv from dual)select lpad(' ',2 * level - 1) || vvfrom tstart with v = 5 --这个查询条件只是过滤整个查询的起点，也就是从哪个节点开始遍历 --如果没有这个过滤条件，那么会尝试从每个值connect by prior v = parent_v;--在start with的条件中使用子查询with tas( select 1 as v,null parent_v,'01' vv from dual union all select 2 as v,1,'02' as vv from dual union all select 3 as v,2,'03' as vv from dual union all select 4 as v,2,'04' as vv from dual union all select 5 as v,1,'05' as vv from dual union all select 6 as v,5,'06' as vv from dual union all select 7 as v,6,'07' as vv from dual union all select 8 as v,5,'08' as vv from dual union all select 9 as v,8,'09' as vv from dual union all select 10 as v,1,'10' as vv from dual union all select 11 as v,10,'11' as vv from dual union all select 12 as v,10,'12' as vv from dual union all select 13 as v,10,'13' as vv from dual)/*LPAD('',2*LEVEL-1)||VV ----------------------------- 05 06 07 08 09 */select lpad(' ', 2 * level - 1) || vvfrom tstart with v = (select v from t where vv = '05')connect by prior v = parent_v;--通过connect by条件删除分支with tas( select 1 as v,null parent_v,'01' vv from dual union all select 2 as v,1,'02' as vv from dual union all select 3 as v,2,'03' as vv from dual union all select 4 as v,2,'04' as vv from dual union all select 5 as v,1,'05' as vv from dual union all select 6 as v,5,'06' as vv from dual union all select 7 as v,6,'07' as vv from dual union all select 8 as v,5,'08' as vv from dual union all select 9 as v,8,'09' as vv from dual union all select 10 as v,1,'10' as vv from dual union all select 11 as v,10,'11' as vv from dual union all select 12 as v,10,'12' as vv from dual union all select 13 as v,10,'13' as vv from dual)/*LPAD('',2*LEVEL-1)||VV ------------------------- 05 08 09 */select lpad(' ', 2 * level - 1) || vvfrom tstart with v = 5connect by prior v = parent_v and vv != '06' --会删除分支6，也即是删除节点6，及其子节点7 --这个条件在查询中间过程中进行过滤，会直接删除分支，而不是某个节点--通过where条件，删除节点with tas( select 1 as v,null parent_v,'01' vv from dual union all select 2 as v,1,'02' as vv from dual union all select 3 as v,2,'03' as vv from dual union all select 4 as v,2,'04' as vv from dual union all select 5 as v,1,'05' as vv from dual union all select 6 as v,5,'06' as vv from dual union all select 7 as v,6,'07' as vv from dual union all select 8 as v,5,'08' as vv from dual union all select 9 as v,8,'09' as vv from dual union all select 10 as v,1,'10' as vv from dual union all select 11 as v,10,'11' as vv from dual union all select 12 as v,10,'12' as vv from dual union all select 13 as v,10,'13' as vv from dual)/*LPAD('',2*LEVEL-1)||VV --------------------------- 05 07 08 09 */select lpad(' ', 2 * level - 1) || vvfrom twhere vv != '06' --会删除节点6，但其子节点7还是存在的，where只是对查询的结果进行过滤，所以还是会返回节点7start with v = 5connect by prior v = parent_v 4、rollup、cube子句、grouping sets、grouping函数、grouping_id函数、group_id函数
--1.rollupwith tas( select 1 as v,'abc' as vv,1 as vvv from dual union all select 2 as v,'def' as vv,2 as vvv from dual union all select 3 as v,'ghi' as vv,3 as vvv from dual)/*V VV SUM(VVV)-------------------- -------------------- ----------1 abc 1 1 小计 1 2 def 2 2 小计 2 3 ghi 3 3 小计 3 总计 总计 6 */--需要特别注意rollup中的字段的顺序，不同顺序会有不同的结果--grouping函数,返回1表示这个值是group by产生的，不是表本身的数据，返回0表示是本身的数据select case when grouping(v) = 1 and grouping(vv) = 1 then '总计' else cast(v as varchar2(20)) end as v, case when grouping(v) = 0 and grouping(vv) = 1 then '小计' when grouping(v) = 1 and grouping(vv) = 1 then '总计' else cast(vv as varchar2(20)) end as vv, sum(vvv) from tgroup by rollup(v,vv);--2.cubewith tas( select 1 as v,'abc' as vv,1 as vvv from dual union all select 2 as v,'def' as vv,2 as vvv from dual union all select 3 as v,'ghi' as vv,3 as vvv from dual)/*V VV SUM(VVV)-------------------- -------------------- ----------1 abc 1 1 v-小计 1 2 def 2 2 v-小计 2 3 ghi 3 3 v-小计 3 vv-小计 abc 1 vv-小计 def 2 vv-小计 ghi 3 总计 总计 6 */--cube中的字段不讲究顺序，结果是一样的--grouping_id(col1,col2,col3...,coln)函数,当n列是group by产生，也就是null时返回1--其实grouping_id就是grouping的一个加强版select case when grouping_id(v,vv) in (0,1) --0表示v与vv都非空，1表示vv列空而v列非空 then cast(v as varchar(20)) when grouping_id(v,vv) = 2 --2表示v列为空，vv为非空 then 'vv-小计' when grouping_id(v,vv) = 3 --v与vv列都为空 then '总计' end as v,case when grouping_id(v,vv) in (0,2) then cast(vv as varchar(20)) when grouping_id(v,vv) = 1 then 'v-小计' when grouping_id(v,vv) = 3 then '总计' end as vv, sum(vvv)from tgroup by cube(v,vv)order by v,vv;--3.grouping sets子句，只返回小计with tas( select 1 as v,'abc' as vv,1 as vvv from dual union all select 2 as v,'def' as vv,2 as vvv from dual union all select 3 as v,'ghi' as vv,3 as vvv from dual)/* V VV SUM(VVV)---------- --- ---------- 1 1 2 2 3 3 abc 1 def 2 ghi 3 *//*其实就相当于:group by v union all group by vv */select v, vv, sum(vvv)from tgroup by grouping sets(v,vv)order by v,vv;--4.group_id函数，没有参数，如果某个分组重复出现n次，那么会返回从0到n-1之间的整数。with tas( select 1 as v,'abc' as vv,1 as vvv from dual union all select 2 as v,'def' as vv,2 as vvv from dual union all select 3 as v,'ghi' as vv,3 as vvv from dual)/* V VV GROUPID SUM(VVV)---------- --- ---------- ---------- 1 abc 0 1 1 1 1 1 0 1 2 def 0 2 2 0 2 2 1 2 3 ghi 0 3 3 0 3 3 1 3 *//*这里其实就是把v和rollup(v,vv)进行group by，但会产生重复值，也就是:group by : v,(v,vv) v,(v,null) => v,null => group_id()返回0 v,(null,null) => v,null => group_id()返回1所以会产生2个v,null。*/select v, vv, group_id() as groupID, sum(vvv)from tgroup by v,rollup(v,vv) order by v,vv;
5、分析函数
create table t(year int not null,month int not null,type_name varchar2(20) not null,emp_id int not null,amount number(10,2));delete from t;insert into t(year,month,type_name,emp_id,amount)select *from ( select 2013 as v1, 1 as v2, 'Book' as v3, 1 as v4, 100 as v5 from dual union all select 2013, 2, 'Book', 1, 200 from dual union all select 2013, 3, 'Book', 1, 300 from dual union all select 2013, 4, 'Book', 1, 400 from dual union all select 2013, 5, 'Book', 1, 500 from dual union all select 2013, 6, 'Book', 1, 600 from dual union all select 2013, 7, 'Book', 1, 700 from dual union all select 2013, 8, 'Book', 1, 800 from dual union all select 2013, 9, 'Book', 1, 900 from dual union all select 2013, 10, 'Book', 1, 1000 from dual union all select 2013, 11, 'Book', 1, null from dual union all select 2013, 12, 'Book', 1, 1000 from dual union all select 2013, 1, 'Magazine', 1, 100 from dual union all select 2013, 2, 'Magazine', 2, 200 from dual union all select 2013, 3, 'Magazine', 1, 300 from dual union all select 2013, 4, 'Magazine', 2, 400 from dual union all select 2013, 5, 'Magazine', 1, 500 from dual union all select 2013, 6, 'Magazine', 2, 600 from dual union all select 2013, 7, 'Magazine', 1, 700 from dual union all select 2013, 8, 'Magazine', 2, 800 from dual union all select 2013, 9, 'Magazine', 1, null from dual union all select 2013, 10, 'Magazine', 2, 1000 from dual union all select 2013, 11, 'Magazine', 2, null from dual union all select 2013, 12, 'Magazine', 1, 800 from dual);commit;--1.排名函数--1.1在降序排列时，默认会把空值排到第一，而在升序排列时会把空值排到最后select year, month, amount, --在值相等的情况下，名次会留下空位 rank() over(order by amount desc) as rank, --不会留有空位，是密集的 dense_rank() over(order by amount desc) as dense_rank, --行号，类似于rownum伪列，但按照某个字段排序后再编号 row_number() over(order by amount desc) as row_number, --按照返回的记录显示行号，与row_number显示的行号不同 rownum, --按照字段排序，根据记录条数/分片数=12/4 = 3，也就是每个片有3条记录，一共4片 ntile(4) over(order by amount desc) as ntilefrom twhere type_name = 'Magazine';--1.2通过nulls first和nulls last来空值null在排序时，显示的位置select year, month, amount, emp_id, --在值相等的情况下，名次会留下空位 rank() over(order by amount desc nulls last) as rank, --不会留有空位，是密集的 dense_rank() over(order by amount desc nulls last) as dense_rank, --行号，类似于rownum伪列，但按照某个字段排序后再编号 row_number() over(order by amount desc nulls last) as row_number, --按照返回的记录显示行号，与row_number显示的行号不同 rownum, --按照emp_id分组，这里每组有6条记录，再按字段排序， --根据记录条数/分片数=6/4 = 1.5，前两个分片每片有2条记录,后2个分片每个1条记录 ntile(5) over(partition by emp_id order by amount desc nulls last ) as ntilefrom twhere type_name = 'Magazine'order by emp_id,ntile;--2.百分点函数、反百分点函数、假想评级分布函数--百分点函数select v, --rank排名 rank() over(order by v desc) as rank, --rank排名号/总的记录数 --如果有多个值相同，那么取相同值中最大的row_number/总的记录数 cume_dist() over(order by v desc) as cume_dist, --(rank排名号-1)/(总的记录数 - 1) --如果有多个值相同，那么取相同值中最小的row_number/总的记录数 percent_rank() over(order by v desc) as percent_rankfrom ( select 600 as v from dual union all select 400 as v from dual union all select 100 as v from dual union all select 300 as v from dual union all select 300 as v from dual)--反百分点函数、假想评级分布函数select --反百分点函数 --在每个分组中检查累积分布的数值，直到找到大于或等于参数的值，与percent_disc相反 percentile_disc(0.8) within group (order by v desc) as percentile_disc, --在每个分组中检查百分比排名的值，直到找到大于或等于参数的值，与percent_rank相反 percentile_cont(0.5) within group (order by v desc) as percentile_count, --假想评级分布函数 --假设v的值为350，那么返回rank排名 rank(350) within group (order by v desc) as rank, --假设v的值为350，那么返回percent_rank的百分比 percent_rank(350) within group (order by v desc) as percent_rankfrom ( select 600 as v from dual union all select 400 as v from dual union all select 100 as v from dual union all select 300 as v from dual union all select 300 as v from dual)--3.窗口函数--下面用的是sum，但avg、count、max、min等函数都适用select year, month, sum(amount) as amount, --计算累积和 sum(sum(amount)) over(order by month rows between unbounded preceding and current row) as cumulative_amount, --计算移动累积和,本月与前3个月销量和 sum(sum(amount)) over(order by month rows between 3 preceding and current row) as moving_amout, --计算中心累积和，也就是本月、前一个月、后一个月的销量总和 sum(sum(amount)) over(order by month rows between 1 preceding and 1 following) as moving_center_amount, --获取窗口的第一条记录 first_value(sum(amount)) over(order by month rows between 1 preceding and 1 following) as first_value_amount, --获取窗口的最后一条记录 last_value(sum(amount)) over(order by month rows between 1 preceding and 1 following) as last_value_amount, --当前记录的向前第1条记录，如果前面没有记录，那么返回null lag(sum(amount),1) over(order by month) as lag_amount, --当前记录的下一条记录，如果后面没有记录，返回null lead(sum(amount),1) over(order by month) as lead_amount, --对null值的不同处理,默认是respect nulls，表示把null正常处理 lag(sum(amount),1) respect nulls over(order by month) as respect_nulls, --ignore nulls表示忽略null,比如要找前一条记录，如果前一条记录是null,那么会跳过这条记录再往前找一条 lag(sum(amount),1) ignore nulls over(order by month) as ignore_nullsfrom tgroup by year,monthorder by year,month;--4.报表函数--下面用到了sum，也适合avg,max,min,countselect month, type_name, sum(amount) as amout, --对group by之后的结果，再次对结果进行group by month求sum sum(sum(amount)) over(partition by month) as month_amount, --对group by之后的结果，再次对结果进行group by type_name求sum sum(sum(amount)) over(partition by type_name) as type_name_amount, --计算某个月某个品类的amount/某个月不分品类的amount ratio_to_report(sum(amount)) over(partition by month) as ratio_to_reportfrom tgroup by month,type_nameorder by month,type_name;--5.first函数、last函数--适用于min、max、count、sum、avgselect --按照amount的和排序，求dense_rank，取排第1的，然后求最小的month min(month) keep (dense_rank first order by sum(amount)) as first_amount, max(month) keep (dense_rank last order by sum(amount)) as last_amountfrom tgroup by monthorder by month;
6、model子句、povit与unpovit
--1.位置标记访问select *from tmodelpartition by (type_name)dimension by (month,year)measures (amount sales_amount)( sales_amount[1,2014] = sales_amount[1,2013], sales_amount[2,2014] = sales_amount[2,2013] + sales_amount[3,2013], sales_amount[3,2014] = round(sales_amount[3,2013] * 1.5, 2))order by type_name,year,month;--2.符号标记访问--注意位置必须要对齐，select type_name,year,month,sales_amountfrom tmodelpartition by (type_name)dimension by (month,year)measures (amount sales_amount)( sales_amount[month = 1,year = 2014] = sales_amount[month = 1,year = 2014], sales_amount[month = 1,year = 2014] = sales_amount[month = 1,year = 2014], sales_amount[month = 1,year = 2014] = round(sales_amount[month = 1,year = 2014] * 1.5,2))order by type_name,year,month;--3.1 between andselect *from tmodelpartition by (type_name)dimension by (month,year)measures (amount sales_amount)( --这里不太清楚为什么如果采用标记访问，会导致不会产生新的1,2014的记录 --sales_amount[month =1,year =2014] = sum(sales_amount)[month between 1 and 3,2013] --必须要用sum函数，否则会报错 sales_amount[1,2014] = sum(sales_amount)[month between 1 and 3,2013]) order by type_name,year,month;--3.2通过any和is any,表示任何维度的数据select *from tmodelpartition by (type_name)dimension by (month,year)measures (amount sales_amount)( --这里不太清楚为什么如果采用标记访问，会导致不会产生新的1,2014的记录 --sales_amount[month =1,year =2014] = sum(sales_amount)[month between 1 and 3,2013] --必须要用sum sales_amount[1,2014] = sum(sales_amount)[any,year is any]) order by type_name,year,month;--3.3 for循环--currentv()函数来访问当前的维度--is present检查单元格是否存在--is not null检查是否是null值select *from tmodelpartition by (type_name)dimension by (month,year)measures (amount sales_amount)( sales_amount[for month from 10 to 12 increment 1,2014] = case when sales_amount[currentv(),2013] is present and sales_amount[currentv(),2013] is not null then sales_amount[currentv(),2013] else 0 end) order by type_name,year,month;--3.3 ignore nav和keep navselect *from tmodel ignore nav --忽略null，自动返回0partition by (type_name)dimension by (month,year)measures (amount sales_amount)( sales_amount[for month from 10 to 12 increment 1,2014] = case when sales_amount[currentv(),2013] is present then sales_amount[currentv(),2013] else 0 end) order by type_name,year,month;--3.4 rules update更新已经存在的值，如果不存在，不会创建，也就是只会更新select *from tmodel ignore nav --忽略null，自动返回0partition by (type_name)dimension by (month,year)measures (amount sales_amount)rules update --返回结果集中，不会包含2014年的数据( sales_amount[for month from 10 to 12 increment 1,2014] = case when sales_amount[currentv(),2013] is present then sales_amount[currentv(),2013] else 0 end) order by type_name,year,month;--4.pivot--4.1 单列转置select *from (select type_name, year, month, amountfrom t)pivot( --虽然有12个月的数据，但这里只需要1-4月份的数据 sum(amount) for month in (1 as jan,2 as feb,3 as mar,4 apr) )order by type_name,year;--4.2 多列转置select *from(select type_name,year,month,amountfrom t)pivot( sum(amount) for (month,type_name) in ( (1,'Book') as jan_book, (2,'Book') as feb_book, (3,'Book') as mar_book ));--5. unpivotSELECT *FROM( select * from ( select type_name, year, month, amount from t ) pivot ( --虽然有12个月的数据，但这里只需要1-4月份的数据 sum(amount) for month in (1 as jan,2 as feb,3 as mar,4 apr) ))unpivot( amount for month in (JAN as 1,FEB as 2,MAR as 3,APR as 4))
- Oracle Database 11g SQL 开发指南学习笔记：高级查询
- 《Oracle Database 11g SQL 开发指南》学习笔记——第六章__子查询
- Oracle Database 11g SQL 开发指南学习笔记：子查询
- Oracle Database 11g SQL 开发指南学习笔记：运行store_schema.sql时报错的问题
- Oracle Database 11g SQL 开发指南学习笔记：使用SQL*Plus
- 《Oracle Database 11g SQL 开发指南》学习笔记——第二章
- Oracle Database 11g SQL 开发指南学习笔记：binary_float和binary_double数据类型
- Oracle Database 11g SQL 开发指南学习笔记：从数据库中检索数据
- Oracle Database 11g SQL 开发指南学习笔记：简单函数
- Oracle Database 11g SQL 开发指南学习笔记：日期、时间的存储和处理
- 《Oracle Database 11g SQL 开发指南》学习笔记——第11章_PL/SQL编程简介
- 《Oracle Database 11g SQL 开发指南》学习笔记——第三章_使用SQL*PLUS
- 《Oracle Database 11g SQL 开发指南》学习笔记——第四章_使用简单函数（一）
- 《Oracle Database 11g SQL 开发指南》学习笔记——第四章_使用简单函数（二）
- Oracle Database 11g SQL 开发指南学习笔记：Oracle 11g r2安装完后SQL Developer无法使用的问题
- Oracle 11g学习笔记--高级查询
- Oracle Database ：Oracle11g SQL开发指南学习笔记之（3）：结构化查询语言
- Oracle 11g SQL开发指南 学习笔记之从数据库表中检索信息
- 系统自带的文件拷贝、移动、删除和重命名操作
- #include与直接写class加类名有何区别
- hibernate 注解关系
- Incompatible namespaceIDs
- 判断是否存在下游单据
- Oracle Database 11g SQL 开发指南学习笔记：高级查询
- js隐藏和显示表格
- [iOS开发必备工具之]简单的全景展示利器panoramagl
- linux 随记
- DBA 经典面试题(1)
- 使用位逻辑运算实现位向量并实现位图排序
- Activity 属性设置
- JAVA 中int类型转String类型的通常方法
- 源代码安装openldap总结
