create table earnings -- 打工赚钱表 
( 
 earnmonth varchar2(6), -- 打工月份 
 area varchar2(20), -- 打工地区 
 sno varchar2(10), -- 打工者编号 
 sname varchar2(20), -- 打工者姓名 
 times int, -- 本月打工次数 
 singleincome number(10,2), -- 每次赚多少钱 
 personincome number(10,2) -- 当月总收入 
) 

insert into earnings values('200912','北平','511601','大魁',11,30,11*30);  
insert into earnings values('200912','北平','511602','大凯',8,25,8*25);  
insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25);  
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);  
insert into earnings values('200912','北平','511605','贱敬',30,11,30*11);  
insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);  
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);  
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);  
insert into earnings values('200912','金陵','511304','小俐',0,18,0);  
insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88);  
insert into earnings values('201001','北平','511601','大魁',0,30,0);  
insert into earnings values('201001','北平','511602','大凯',14,25,14*25);  
insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25);  
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);  
insert into earnings values('201001','北平','511605','贱敬',21,11,21*11);  
insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);  
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);  
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);  
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);  
insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88);  
commit; 

select * from earnings; 

select earnmonth, area, sum(personincome)  
from earnings  
group by earnmonth,area;  

select earnmonth, area, sum(personincome)  
from earnings  
group by rollup(earnmonth,area); 

select earnmonth, area, sum(personincome)  
from earnings  
group by cube(earnmonth,area)  
order by earnmonth,area nulls last; 

select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,  
    decode(grouping(area),1,'全部地区',area) 地区, sum(personincome) 总金额  
from earnings  
group by cube(earnmonth,area)  
order by earnmonth,area nulls last; 

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   
    rank() over (partition by earnmonth,area order by personincome desc) 排名  
from earnings; 

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   
    dense_rank() over (partition by earnmonth,area order by personincome desc) 排名  
from earnings;

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   
    row_number() over (partition by earnmonth,area order by personincome desc) 排名  
from earnings;

select earnmonth 月份,area 地区,sname 打工者,   
    sum(personincome) over (partition by earnmonth,area order by personincome) 总收入  
from earnings;

select distinct earnmonth 月份, area 地区,  
    max(personincome) over(partition by earnmonth,area) 最高值,  
    min(personincome) over(partition by earnmonth,area) 最低值,  
    avg(personincome) over(partition by earnmonth,area) 平均值,  
    sum(personincome) over(partition by earnmonth,area) 总额  
from earnings;

select earnmonth 本月,sname 打工者,  
    lag(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 上月,  
    lead(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 下月  
from earnings;