Oracle Database 11g SQL开发指南学习笔记：高级查询(四)
句、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之间的整数。
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 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, 'Bo