sql over(partition by) 开窗函数的使用

转自：https://www.cnblogs.com/huangchuxian/p/7808051.html

 

sql over(partition by) 开窗函数的使用

patition by  在group by分组汇总的前提下，再汇总一次

1、patition by 1   汇总所有数据

 1 select * from stu 

1 select
2 class,
3 sum(score) 班级总分,
4 sum(sum(score)) over(partition by 1) 年级总分,
5 cast(cast(sum(score)*100/nullif(sum(sum(score)) over(partition by 1),0) as numeric(18,2)) as varchar(50))+'%' as 班级总分占比
6 from stu
7 group by class

 

2、patition by 字段   根据该字段汇总

 1 select * from stu 

 

1 select
2 class,
3 name,
4 sum(score) 个人总分,
5 sum(sum(score)) over(partition by name) 个人总分,
6 sum(sum(score)) over(partition by class) 班级总分,
7 sum(sum(score)) over(partition by 1) 年级总分
8 from stu
9 group by class,name