sql分组后排序计算

用法：RANK() OVER(PARTITION BY 分组字段 ORDER BY 排序字段 )

例子：要得到n4列

 

 

---创建测试数据
create table tb(n1 varchar2(40) ,n2 varchar2(40),n3 int);
insert into tb
select '301','101','100' from dual
union all
select '301','102','100' from dual
union all
select '301','103','100' from dual
union all
select '302','102','300'from dual
union all
select '302','103','300'from dual;

select n1,n2,n3 from tb;
---按照n1分组，n2排序得到排序号，排序号乘以10再加上n3
select x.*, x.n3 + (row_number() over (partition by x.n1 order by x.n2))*10 as n4
from tb x

----使用嵌套的方式得到n3的累计数量

 

 

drop table tb;