select sum(列名)over(partition by 累加条件列 group by 分组列1,分组列2……) total from 表;
一、创建表
Create Table product(
Id Number(9),
pro_name VARCHAR2(50),
pro_date Date,
year_mon VARCHAR2(12),
oil Number(9),
gas Number(9),
water Number(9)9)
二、查询语句:
Select t.Pro_Name,
t.Year_Mon YM,
Substr(t.Year_Mon, 1,4) Y,
t.oil,
Sum(t.oil)over(PartitionBy Substr(t.Year_Mon,1,4)Order By Substr(t.Year_Mon,1,4),t.Year_Mon) total
From Product t
三、查询结果:
pro_name | ym | y | oil | total | |
1 | aa | 201001 | 2,010 | 14 | 14 |
2 | aa | 201002 | 2,010 | 42 | 56 |
3 | aa | 201003 | 2,010 | 14 | 70 |
4 | aa | 201004 | 2,010 | 33 | 103 |
5 | aa | 201005 | 2,010 | 42 | 145 |
6 | aa | 201006 | 2,010 | 25 | 170 |
7 | aa | 201007 | 2,010 | 26 | 196 |
8 | aa | 201008 | 2,010 | 34 | 230 |
9 | aa | 201009 | 2,010 | 38 | 268 |