Oracle Database 11g SQL开发指南学习笔记：高级查询 电脑资料

Oracle Database 11g SQL开发指南学习笔记：高级查询 电脑资料 Oracle Database 11g SQL开发指南学习笔记：高级查询 oracle database 11g sql开发指南学习笔记：高级查询 1、集合操作sql -1.intersect with t as ( select 1 as v,abc as vv from dual union all select 1 ,abc from dual union all select 2,def from dual ), tt as ( select 1 as v,abc as vv from dual union all select 1,abc from dual union all select 3,def from dual ) /* 只返回一条记录，说明求交集后，会去重， oracle database 11g sql开发指南学习笔记：高级查询 。 从执行计划中看，会首先会对第一个表有一个sort操作，选项是unique，也就是去重。 然后第二个表，也是一样。 也就是先把每个表的记录进行排序去重，然后再求交集 v vv - - 1 abc */ select v,vv from t intersect select v,vv from tt -2.minus with t as ( select 1 as v,abc as vv from dual union all select 1 ,abc from dual union all select 2,def from dual ), tt as ( 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 minus select v,vv from tt 2、decode函数、translate函数sql 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、层次化查询sql -1.层次查询：自顶向下、自底向上 -自顶向下 with t as ( 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) | vv from t start with v = 1 connect by prior v = parent_v; -自底向上 with t as ( 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是伪劣，第一层恢复返回1 select lpad( ,2 * level - 1) | vv from t start with v = 9 connect by v = prior parent_v; -connect by prior parent_v = v 效果是一样的 -2.过滤 -通过start with的条件，从非根结点开始遍历 with t as ( 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) | vv from t start with v = 5 -这个查询条件只是过滤整个查询的起点，也就是从哪个节点开始遍历 -如果没有这个过滤条件，那么会尝试从每个值 connect by prior v = parent_v; -在start with的条件中使用子查询 with t as ( 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) | vv from t start with v = (select v from t where vv = 05) connect by prior v = parent_v; -通过connect by条件删除分支 with t as ( 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) | vv from t start with v = 5 connect by prior v = parent_v and vv != 06 -会删除分支6，也即是删除节点6，及其子节点7 -这个条件在查询中间过程中进行过滤，会直接删除分支，而不是某个节点 -通过where条件，删除节点 with t as ( 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) | vv from t where vv != 06 -会删除节点6，但其子节点7还是存在的，where只是对查询的结果进行过滤，所以还是会返回节点7 start with v = 5 connect by prior v = parent_v 4、rollup、cube子句、grouping sets、grouping函数、grouping_id函数、group_id函数sql -1.rollup with t as ( 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 abc1 1 小计 1 2 def2 2 小计 2 3 ghi3 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 t group by rollup(v,vv); -2.cube with t as ( 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 abc1 1 v-小计 1 2 def2 2 v-小计 2 3 ghi3 3 v-小计 3 vv-小计 abc1 vv-小计 def2 vv-小计 ghi3 总计 总计 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 t group by cube(v,vv) order by v,vv; -3.grouping sets子句，只返回小计 with t as ( 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 t group by grouping sets(v,vv) order by v,vv; -4.group_id函数，没有参数，如果某个分组重复出现n次，那么会返回从0到n-1之间的整数， 电脑资料 oracle database 11g sql开发指南学习笔记：高级查询(s:/)。 with t as ( 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 t group by v,rollup(v,vv) order by v,vv; 5、分析函数sql 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 xx as v1, 1 as v2, book as v3, 1 as v4, 100 as v5 from dual union all select xx, 2, book, 1, 200 from dual union all select xx, 3, book, 1, 300 from dual union all select xx, 4, book, 1, 400 from dual union all select xx, 5, book, 1, 500 from dual union all select xx, 6, book, 1, 600 from dual union all select xx, 7, book, 1, 700 from dual union all select xx, 8, book, 1, 800 from dual union all select xx, 9, book, 1, 900 from dual union all select xx, 10, book, 1, 1000 from dual union all select xx, 11, book, 1, null from dual union all select xx, 12, book, 1, 1000 from dual union all select xx, 1, magazine, 1, 100 from dual union all select xx, 2, magazine, 2, 200 from dual union all select xx, 3, magazine, 1, 300 from dual union all select xx, 4, magazine, 2, 400 from dual union all select xx, 5, magazine, 1, 500 from dual union all select xx, 6, magazine, 2, 600 from dual union all select xx, 7, magazine, 1, 700 from dual union all select xx, 8, magazine, 2, 800 from dual union all select xx, 9, magazine, 1, null from dual union all select xx, 10, magazine, 2, 1000 from dual union all select xx, 11, magazine, 2, null from dual union all select xx, 12, magazine, 1, 800 from dual ); mit; -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 ntile from t where 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 ntile from t where 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_rank from ( 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_rank from ( 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_nulls from t group by year,month order by year,month; -4.报表函数 -下面用到了sum，也适合avg,max,min,count select 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_report from t group by month,type_name order by month,type_name; -5.first函数、last函数 -适用于min、max、count、sum、avg select -按照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_amount from t group by month order by month; 6、model子句、povit与unpovitsql -1.位置标记访问 select * from t model partition by (type_name) dimension by (month,year) measures (amount sales_amount) ( sales_amount1,xx = sales_amount1,xx, sales_amount2,xx = sales_amount2,xx + sales_amount3,xx, sales_amount3,xx = round(sales_amount3,xx * 1.5, 2) ) order by type_name,year,month; -2.符号标记访问 -注意位置必须要对齐， select type_name,year,month,sales_amount from t model partition by (type_name) dimension by (month,year) measures (amount sales_amount) ( sales_amountmonth = 1,year = xx = sales_amountmonth = 1,year = xx, sales_amountmonth = 1,year = xx = sales_amountmonth = 1,year = xx, sales_amountmonth = 1,year = xx = round(sales_amountmonth = 1,year = xx * 1.5,2) ) order by type_name,year,month; -3.1 between and select * from t model partition by (type_name) dimension by (month,year) measures (amount sales_amount) ( -这里不太清楚为什么如果采用标记访问，会导致不会产生新的1,xx的记录 -sales_amountmonth =1,year =xx = sum(sales_amount)month between 1 and 3,xx -必须要用sum函数，否则会报错 sales_amount1,xx = sum(sales_amount)month between 1 and 3,xx ) order by type_name,year,month; -3.2通过any和is any,表示任何维度的数据 select * from t model partition by (type_name) dimension by (month,year) measures (amount sales_amount) ( -这里不太清楚为什么如果采用标记访问，会导致不会产生新的1,xx的记录 -sales_amountmonth =1,year =xx = sum(sales_amount)month between 1 and 3,xx -必须要用sum sales_amount1,xx = sum(sales_amount)any,year is any ) order by type_name,year,month; -3.3 for循环 -currentv()函数来访问当前的维度 -is present检查单元格是否存在