先在数据库中建表
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)
)
查询语句如下
Select t.Pro_Name,
t.Year_Mon YM,
Substr(t.Year_Mon, 1, 4) Y,
t.oil,
Sum(t.oil)over(Partition By Substr(t.Year_Mon, 1, 4) Order By Substr(t.Year_Mon, 1, 4),t.Year_Mon) total
From Product t
为什么能够得到月累计的效果?我觉得Partition By 年份的话,Total字段应该显示这年的总量,而不是这年内每月累加的量。请高手指点迷津。下面是运行结果:
------解决方案--------------------
因为t.Year_Mon YM作为了查询字段
------解决方案--------------------
其实是每一年进行累积求和的,只不过在每一年中又按月份进行了细分,一下数据就能看出区别:
with Product as
(select 'aa' pro_name, '201001' year_mon, 14 oil
from dual
union all
select 'aa' pro_name, '201002' year_mon, 42 oil
from dual
union all
select 'aa' pro_name, '201003' year_mon, 14 oil
from dual
union all
select 'aa' pro_name, '201004' year_mon, 23 oil
from dual
union all
select 'aa' pro_name, '201205' year_mon, 134 oil
from dual
union all
select 'aa' pro_name, '201206' year_mon, 54 oil from dual)
Select t.Pro_Name,
t.Year_Mon YM,
Substr(t.Year_Mon, 1, 4) Y,
t.oil,
Sum(t.oil) over(Partition By Substr(t.Year_Mon, 1, 4) Order By Substr(t.Year_Mon, 1, 4), t.Year_Mon) total
From Product t