------解决思路----------------------
理解LZ 的意思，就累计求和？？？
drop table test
go
create table test
(
st varchar(10) ,
dates varchar(10) ,
C1 decimal(10,2) ,
C2 decimal(10,2)
)
go
insert into test(st,dates,C1,C2) values
('1001','2014-01-31',1,0),
('1001','2014-02-28',1,0),
('1001','2014-03-31',1,0),
('1001','2014-04-30',1,0),
('1001','2014-05-31',1,0),
('1002','2014-01-31',1,0),
('1002','2014-02-28',0.5,0),
('1002','2014-03-31',0.7,0),
('1002','2014-04-30',0.9,0),
('1002','2014-05-31',1,0)
go
select * from test
go
with m as
(
select ROW_NUMBER() over (partition by st order by dates) as rn ,
st , dates , C1 , C2
from test
)
,
cte1 as
(
select m.rn , m.st , m.dates , m.C1 , C1 As C2 from m where rn = 1
union all
select m.rn , m.st , m.dates , m.C1 , cast(isnull(cte1.C2,0.0) + isnull(m.C1,0.0) as decimal(10,2))
from cte1 inner join m on m.rn = cte1.rn + 1 and cte1.st = m.st
)
select * from cte1
order by 2,1
go
(10 行受影响)
st dates C1 C2
---------- ---------- --------------------------------------- ----------
1001 2014-01-31 1.00 0.00
1001 2014-02-28 1.00 0.00
1001 2014-03-31 1.00 0.00
1001 2014-04-30 1.00 0.00
1001 2014-05-31 1.00 0.00
1002 2014-01-31 1.00 0.00
1002 2014-02-28 0.50 0.00
1002 2014-03-31 0.70 0.00
1002 2014-04-30 0.90 0.00
1002 2014-05-31 1.00 0.00
(10 行受影响)
rn st dates C1 C2
-------------------- ---------- ---------- --------------------------------------- ---------
1 1001 2014-01-31 1.00 1.00
2 1001 2014-02-28 1.00 2.00
3 1001 2014-03-31 1.00 3.00
4 1001 2014-04-30 1.00 4.00
5 1001 2014-05-31 1.00 5.00
1 1002 2014-01-31 1.00 1.00
2 1002 2014-02-28 0.50 1.50
3 1002 2014-03-31 0.70 2.20
4 1002 2014-04-30 0.90 3.10
5 1002 2014-05-31 1.00 4.10
(10 行受影响)
------解决思路----------------------
Update T Set C2=(Select SUM(C1) From T B Where B.Datas<T.Datas)