存储过程 --- 3 --- 常见函数学习

建表：

create table earnings
(    earnmonth  varchar2(32),--打工月份
     area varchar2(10),--地区
     sno varchar2(10),--打工者编号
     sname varchar(19),--名字
     times int,--次数
     singleincome  number(10,2),--每次多少钱
     personincome  number---当月总收入入
)


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,6.25*30);
insert into earnings values('200912','北京','511604','大亮',16,8.25,16*8.25);
insert into earnings values('200912','北京','511605','小王',11,30,11*30);
insert into earnings values('200912','南京','511301','小雨',15,12.25,15*12.25);
insert into earnings values('200912','南京','511302','小饭',17,16.67,17*16.67);
insert into earnings values('200912','南京','511303','小妮',27,33.33,17*33.33);
insert into earnings values('200912','南京','511304','小第',16,18,16*18);
insert into earnings values('200912','南京','511305','小阳',11,19.88,11*19.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,6.25*19);
insert into earnings values('201001','北京','511604','大亮',7,8.25,7*8.25);
insert into earnings values('201001','北京','511605','小王',8,30,8*30);
insert into earnings values('201001','南京','511301','小雨',6,12.25,6*12.25);
insert into earnings values('201001','南京','511302','小饭',11,16.67,11*16.67);
insert into earnings values('201001','南京','511303','小妮',13,33.33,13*33.33);
insert into earnings values('201001','南京','511304','小第',20,18,20*18);
insert into earnings values('201001','南京','511305','小阳',30,19.88,30*19.88);
SELECT * FROM earnings


1 .  sum  函数 统计总和
 按照月份  统计每个地区的总收入
select  earnmonth ,area,sum(personincome) from earnings group by earnmonth,area;
------------------------------------------------------------------------------------------------------
2 .  rollup 函数  
 -- http://blog.itpub.net/519536/viewspace-610995/
 --ROLLUP 分组函数可以理解为Group By分组函数封装后的精简用法
 按照月份 地区统计收入
 -- group by 函数后面接rollup  是在纯粹的 group by 的分组上再 加上对earnmonth的汇总统计
select  earnmonth ,area,sum(personincome) from earnings group by rollup(earnmonth,area);
 -- *****注意earnmonth，area 谁在前后是有区别的，影响汇总的结果是月份还是区域，
 -- 实现上面效果的普通的sql 语句
select earnmonth ,area,sum(personincome) from earnings group by earnmonth,area
union all 
select earnmonth ,null,sum(personincome) from earnings group by earnmonth
union all 
select null ,null,sum(personincome) from earnings 
order by 1,2;
 --其他知识 上面语句中想问问这个是什么意思??
order by 1,2 根据第一列和第二列排序 相当于 order by earnmonth ,area
 -- UNION 用法 http://www.w3school.com.cn/sql/sql_union.asp
------------------------------------------------------------------------------------------------------
3 . cube 函数  http://blog.itpub.net/519536/viewspace-610997/
 -- https://msdn.microsoft.com/zh-cn/library/ms175939
按照月份 地区  进行收入总收入
select earnmonth ,area,sum(personincome) from earnings group by cube(earnmonth,area) order by 1,2;
 --group by 后面接cube 是对earnmonth汇总统计的基础上对area 在统计
 --group by  是分组函数，按照earnmonth，area先后次序分组
 --上面的查询 是先按照earnmonth 分组，在earnmonth 内部再按照area分组，并且在area组内统计personincome总和
------------------------------------------------------------------------------------------------------
4 . grouping 函数  http://millerrch.iteye.com/blgroupingog/1882423
 -- 上面的rollup 和 cube 函数都会对结果产生null，这时 可用grouping 来确认该记录是哪个字段得出来的
select earnmonth ,area,sum(personincome) from earnings group by rollup(earnmonth,area);  -- 这是有空值的
 --grouping 函数的用法 带一个参数，参数是字段名称如果是本身的结果（列值非空）就返回0，如果是合计的结果（列值为空）则返回1
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(personincome) from earnings group by rollup(earnmonth,area);
------------------------------------------------------------------------------------------------------
5 . rank 函数,dense_rank 函数,row_number 函数
 -- http://www.jb51.net/article/85125.htm
 按照 月份 地区 求打工收入排序
 --   rank 排名会有并列，两个第一名 会按照人数跳跃产生名次  1,1,3.。。。。。。。  
 --http://www.jb51.net/article/51627.htm
select  earnmonth ,area,sname,personincome,rank () over(partition by earnmonth,area order by personincome desc ) 排名 from earnings;
 -- dense_rank 排名会有并列，两个第一名 但不会按照人数跳跃产生名次，1,1,2.。。。。。。。
select  earnmonth ,area,sname,personincome,dense_rank () over(partition by earnmonth,area order by personincome desc ) 排名 from earnings;
 -- row_number 排名不会有并列，既是两个数据一样，排名也不一样，1，2，3，4。。。。。。
 -- http://blog.csdn.net/tanzuai/article/details/42391885
select  earnmonth ,area,sname,personincome,row_number () over(partition by earnmonth,area order by personincome desc ) 排名 from earnings;
------------------------------------------------------------------------------------------------------
SUM  累计求和
select earnmonth ,area,sname,personincome,sum (personincome) over(partition by sname order by personincome asc) "总收入" from earnings;
------------------------------------------------------------------------------------------------------
sum,avg.max,min综合运用
--按照月份，地区 求打工收入最高值，最低，平均，总和

select distinct earnmonth "月份" ,area "地区",max(personincome) over (partition by earnmonth ,area ) "max",min(personincome) over (partition by earnmonth ,area ) "min",avg(personincome) over (partition by earnmonth ,area ) "avg",sum(personincome) over (partition by earnmonth ,area ) "sum" from earnings;

希望对你有帮助，祝你有一个好心情，加油！

若有错误、不全、可优化的点，欢迎纠正与补充！