这样的求和的SQL语句怎么写（高手请进！谢谢）
如表A：
Select * from A
Date_ CodeNo Code_ Num_
2007-01-01 AA01 AA 10
2007-01-02 AA02 AA 15
2007-01-02 AA03 AA -12
2007-01-03 AA02 AA 20
2007-01-04 AA05 AA -18
现在我要得到以下这样的结果：
Date_ CodeNo Code_ Num_01 Num_02 Num_03
2007-01-01 AA01 AA 10 0 10
2007-01-02 AA02 AA 15 0 25
2007-01-02 AA03 AA 0 -12 13
2007-01-03 AA04 AA 20 0 33
2007-01-04 AA05 AA 0 -18 15
即：第一行中最后一个数等于前两数之和(Num_01+Num_02=Num_03)；从第二个数开始，最后一个数等于上一行最后一个数加上同行倒数第三个减去同行倒数第二个数的
和(上行的Num_03+本行的Num_01-本行的Num_02=本行的Num_03)
谢谢。
[解决办法]
declare @ta table(Date_ varchar(10),CodeNo varchar(5), Code_ varchar(5), Num_ int)
insert @ta select '2007-01-01 ', 'AA01 ', 'AA ', 10
insert @ta select '2007-01-02 ', 'AA02 ', 'AA ', 15
insert @ta select '2007-01-02 ', 'AA03 ', 'AA ', -12
insert @ta select '2007-01-03 ', 'AA04 ', 'AA ', 20
insert @ta select '2007-01-04 ', 'AA05 ', 'AA ', -18
select Date_,CodeNo,Code_,
Num_01=case when Num_> 0 then Num_ else 0 end,
Num_02=case when Num_ <0 then Num_ else 0 end,
Num_03=(select sum(num_) from @ta where Code_=a.Code_ and CodeNo!> a.CodeNo)
from @ta a
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
Date_ CodeNo Code_ Num_01 Num_02 Num_03
---------- ------ ----- ----------- ----------- -----------
2007-01-01 AA01 AA 10 0 10
2007-01-02 AA02 AA 15 0 25
2007-01-02 AA03 AA 0 -12 13
2007-01-03 AA04 AA 20 0 33
2007-01-04 AA05 AA 0 -18 15
(5 行受影响)
[解决办法]
--借用roy_88(中国风_燃烧你的激情！！！) 的數據
declare @A table(Date_ varchar(10),CodeNo varchar(5), Code_ varchar(5), Num_ int)
insert @A select '2007-01-01 ', 'AA01 ', 'AA ', 10
insert @A select '2007-01-02 ', 'AA02 ', 'AA ', 15
insert @A select '2007-01-02 ', 'AA03 ', 'AA ', -12
insert @A select '2007-01-03 ', 'AA04 ', 'AA ', 20
insert @A select '2007-01-04 ', 'AA05 ', 'AA ', -18
Select
Date_,
CodeNo,
Code_,
(Case When Num_ > 0 Then Num_ Else 0 End) As Num_01,
(Case When Num_ <= 0 Then Num_ Else 0 End) As Num_02,
IsNull((Select SUM(Num_) From @A Where Date_ < T.Date_ Or (Date_ = T.Date_ And CodeNo < T.CodeNo)), 0) + Num_ As Num_03
From
@A T
--Result
/*
Date_CodeNoCode_Num_01Num_02Num_03
2007-01-01AA01AA10010
2007-01-02AA02AA15025
2007-01-02AA03AA0-1213
2007-01-03AA04AA20033
2007-01-04AA05AA0-1815
*/
[解决办法]
你的表中需要有字段來保證數據的順序。
declare @A table(Date_ varchar(10),CodeNo varchar(5), Code_ varchar(5), Num_ int)
insert @A select '2007-01-01 ', 'AB01 ', 'AA ', 10
insert @A select '2007-01-02 ', 'EA02 ', 'AA ', 15
insert @A select '2007-01-02 ', 'AF03 ', 'AA ', -12
insert @A select '2007-01-03 ', 'EG02 ', 'AA ', 20
insert @A select '2007-01-04 ', 'BH05 ', 'AA ', -18
Select
Date_,
CodeNo,
Code_,
(Case When Num_ > 0 Then Num_ Else 0 End) As Num_01,
(Case When Num_ <= 0 Then Num_ Else 0 End) As Num_02,
IsNull((Select SUM(Num_) From @A Where Date_ < T.Date_ Or (Date_ = T.Date_ And CodeNo > T.CodeNo)), 0) + Num_ As Num_03
From
@A T
--Result
/*
Date_CodeNoCode_Num_01Num_02Num_03
2007-01-01AB01AA10010
2007-01-02EA02AA15025
2007-01-02AF03AA0-1213
2007-01-03EG02AA20033
2007-01-04BH05AA0-1815
*/
[解决办法]
如果你的數據是雜亂的，不能保證順序的話，就只能借用臨時表了。
declare @A table(Date_ varchar(10),CodeNo varchar(5), Code_ varchar(5), Num_ int)
insert @A select '2007-01-01 ', 'AB01 ', 'AA ', 10
insert @A select '2007-01-02 ', 'EA02 ', 'AA ', 15
insert @A select '2007-01-02 ', 'AF03 ', 'AA ', -12
insert @A select '2007-01-03 ', 'EG02 ', 'AA ', 20
insert @A select '2007-01-04 ', 'BH05 ', 'AA ', -18
Select ID = Identity(Int, 1, 1), * Into #T From @A
Select
Date_,
CodeNo,
Code_,
(Case When Num_ > 0 Then Num_ Else 0 End) As Num_01,
(Case When Num_ <= 0 Then Num_ Else 0 End) As Num_02,
(Select SUM(Num_) From #T Where ID <= T.ID) As Num_03
From
#T T
Drop Table #T
--Result
/*
Date_CodeNoCode_Num_01Num_02Num_03
2007-01-01AB01AA10010
2007-01-02EA02AA15025
2007-01-02AF03AA0-1213
2007-01-03EG02AA20033
2007-01-04BH05AA0-1815
*/
