rollup、cube、grouping、rank、dense_rank、row_number函数简介

创建一张earings表

create table earings
(
earnmonth varchar2(6),
area varchar2(20),
sname varchar2(20),
income number(10,2)

)

insert into earings values('200912','广州','张1',1100);
insert into earings values('200912','广州','张2',1200);
insert into earings values('200912','广州','张3',1300);
insert into earings values('200912','广州','张4',1400);
insert into earings values('200912','广州','张5',1500);


insert into earings values('201001','广州','张1',1100);
insert into earings values('201001','广州','张2',1200);
insert into earings values('201001','广州','张3',1300);
insert into earings values('201001','广州','张4',1400);
insert into earings values('201001','广州','张5',1500);


insert into earings values('200912','北京','李1',2100);
insert into earings values('200912','北京','李2',2200);
insert into earings values('200912','北京','李3',2300);
insert into earings values('200912','北京','李4',2400);
insert into earings values('200912','北京','李5',2500);


insert into earings values('201001','北京','李1',2100);
insert into earings values('201001','北京','李2',2200);
insert into earings values('201001','北京','李3',2300);
insert into earings values('201001','北京','李4',2400);
insert into earings values('201001','北京','李5',2500);

1、rollup函数

按照月份、地区统计收入

小结：group by后面接rollup是纯粹的group by分组上对earnmonth的汇总进行统计

select earnmonth,area,sum(income) from earings group by rollup(earnmonth,area)

2、cube函数

按照月份、地区收入总汇总

小结：group by 后面接cube是对earnmonth汇总统计基础上对area进行再统计

select earnmonth,area,sum(income) from earings group by cube(earnmonth,area) order by earnmonth

3、grouping

以cube函数为基础上，在空白处加上内容

select earnmonth,
（case when((grouping(area)=1) and grouping(earnmonth)=0) then '月份小计'
       when ((grouping(area)=1) and grouping(earnmonth)=1) then '总计' 
         else area end）as area,
sum(income) from earings group by cube(earnmonth,area) order by earnmonth

4、rank

按照月份、地区、收入进行排序

select earnmonth, area,sname,
rank() over (partition by earnmonth,area order by income desc)
from earings  

5、dense_rank与rank类似，如果使用rank函数上图中的李5和李4的工资是相同的他们的排名都是1，而李3的排名是3。而如果是用dense_rank函数，那么而李3的排名是还是2，但是李5和李4的排名分别是1和1

6、row_number

不管你是工资是否有相同的，我都会按照顺序进行排名

select earnmonth, area,sname,income,
row_number() over (partition by earnmonth,area order by income desc)
from earings