试试这个 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)) total From Product t 应该可以求和
其实是每一年进行累积求和的,只不过在每一年中又按月份进行了细分,一下数据就能看出区别: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
谢谢两位前辈,也就是说如果把 Order By Substr(t.Year_Mon, 1, 4),t.Year_Mon 换成 Order By Substr(t.Year_Mon, 1, 4) 的话,就是按照年(不细化到月份)统计总量的吗?语句是我在网上看到的,没有自己试,但是没太看明白
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)) total
From Product t
应该可以求和
(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
Order By Substr(t.Year_Mon, 1, 4),t.Year_Mon
换成
Order By Substr(t.Year_Mon, 1, 4)
的话,就是按照年(不细化到月份)统计总量的吗?语句是我在网上看到的,没有自己试,但是没太看明白