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

- 日理万妓 2022-06-17 14:07 116阅读 0赞

建表：

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;

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

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