oracle sum(col1) over(partition by col2 order by col3):实现分组递增汇总

应公司业务要求，需要对数据进行分组汇总做辅助列进行查询

所以使用到了sum(col1) over(partition by col2 order by col3)函数，为了学习与提高在此进行记录。

1、准备数据源

create table test01 (
  name  varchar(20),
  dep_no varchar(10),
  salary  number(20,2)            
);

2、插入数据

insert into test01(name,dep_no,salary) values('张三','0010',12000);
insert into test01(name,dep_no,salary) values('李四','0010',12500);
insert into test01(name,dep_no,salary) values('王五','0020',13000);
insert into test01(name,dep_no,salary) values('赵六','0020',11000);
insert into test01(name,dep_no,salary) values('田七','0020',10000);
insert into test01(name,dep_no,salary) values('如花','0030',8000);
insert into test01(name,dep_no,salary) values('阿卡','0030',9000);
insert into test01(name,dep_no,salary) values('似玉','0030',9500);
insert into test01(name,dep_no,salary) values('撒人','0030',8800);
insert into test01(name,dep_no,salary) values('三忍','0030',6000);
insert into test01(name,dep_no,salary) values('三舞','0030',6600);

3、进行查询

select t.name
             ,t.salary
             ,t.dep_no
             ,sum(t.salary) over(partition by t.dep_no order by t.name)
   from test01 t;

　　查询结果如下图：

　　

这里对dep_no进行分组，根据name进行排序，然后统计每组递增汇总结果！