求个SQL

[toxuecheng111]

2008 10
2008 20
2009 30
2009 40
转换成
2008 10
2008 30
2009 60
2009 100 应该如何写?

[喜-喜]

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
value int
)
go
--插入测试数据
insert into tb select 2008,10
union all select 2008,20
union all select 2009,30
union all select 2009,40
go
--代码实现

;with cte as(select idd=row_number()over(order by getdate()),* from tb)
select id,value=(select sum(value) from cte where idd<=t.idd)
from cte t


/*测试结果

id value
---------------------
2008	10
2008	30
2009	60
2009	100

(4 行受影响)
*/

[快溜]

with cte as
(
  select row_number()over(order by getdate()) no,[date],num from tb
)

select [date],num=(select sum(num) from cte where no<=t.no)
from cte t

[叶子]

declare @table table (col1 int,col2 int)
insert into @table
select 2008,10 union all
select 2008,20 union all
select 2009,30 union all
select 2009,40

select col1,
col2=(select sum(col2) from @table where col1<=t.col1 and col2<=t.col2) 
from @table t
/*
col1        col2
----------- -----------
2008        10
2008        30
2009        60
2009        100
*/

[chuanzhang5687]

select col1,
col2=(select sum(col2) from tableName where col1<=t.col1 and col2<=t.col2) 
from tableName t

[javatemptation]

use tempdb;
/*
create table t1
(
	[date] nvarchar(10) not null,
	[id] int not null
);
insert into t1([date],[id])
values
('2008',10),
('2008',20),
('2009',30),
('2009',40);
*/
select t1.[date],
(select SUM(id) from t1 as t2 where t1.id >= t2.id) as [id]
from t1;

[etunjour]

create table t_1(a int, 
                b int)
insert into t_1 values(2008,10)
insert into t_1 values(2008,20)
insert into t_1 values(2009,30)
insert into t_1 values(2009,40)

select * from t_1

select a,
b=(select sum(b) from t_1 where a<=t.a and b<=t.b) 
from t_1 t

[一直学习]

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
value int
)
go
--插入测试数据
insert into tb select 2008,10
union all select 2008,20
union all select 2009,30
union all select 2009,40
go
--代码实现

with
cr as
(select ROW_NUMBER()OVER(order by getdate()) as [tid],* from tb)
select id,(select sum(value) from cr where tid<= a.tid) as [value] from cr as a

[一直学习]

http://blog.csdn.net/sgear/archive/2011/02/27/6212140.aspx
行列转换