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

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

1、patition by 1   汇总所有数据

 select * from stu 

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

 

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

 select * from stu 

 

select
class,
name,
sum(score) 个人总分,
sum(sum(score)) over(partition by name) 个人总分,
sum(sum(score)) over(partition by class) 班级总分,
sum(sum(score)) over(partition by 1) 年级总分
from stu
group by class,name