当前位置: 代码迷 >> Sql Server >> 求一sql查询思路,
  详细解决方案

求一sql查询思路,

热度:94   发布时间:2016-04-27 17:04:15.0
求一sql查询思路,在线等~~~
例表:
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

  相关解决方案