例表：
table1
字段1 year int
地段2 value int
字段1数值为1994~2005的四位数字对应年,
每一年对应一个数字,
现在我想查询按年累加的同时sum(value),
year value
1994 10
1995 15
1996 20
查出来应该是这样
year value
1994 10
1995 25
1996 45
请问写这个查询的思路是什么呢?最好用一个sql实现,谢谢大家了~~!!!
------解决方案--------------------
create table T([year] int, value int)
insert T select 1994, 10
union all select 1995, 15
union all select 1996, 20
select [year], value=(select sum(value) from T where [year] <=A.[year]) from T as A
--result
year value
----------- -----------
1994 10
1995 25
1996 45
(3 row(s) affected)
------解决方案--------------------
select year, value=sum(value) from tbName a where year <a.year group by year
------解决方案--------------------
--环境
create table yearvalue
(
[year] int,
value int
)
insert into yearvalue select 1994, 10
insert into yearvalue select 1995, 15
insert into yearvalue select 1996, 20
--语句
select [year],value = (select sum(value) from yearvalue where [year] <= a.[year] )
from yearvalue a
--结果
1994 10
1995 25
1996 45
------解决方案--------------------
select [year], value=(select sum(value)/(select count(*) from T as B where B.[year] <=A.[year]) from T where [year] <=A.[year]) from T as A