如表A:
Select * from ADate_ 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)谢谢。
Select * from ADate_ 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)谢谢。
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', -18select 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 行受影响)
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 A Where Date_ <= T.Date_) As Num_03
From
A T
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', -18Select
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_ 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
*/
如表A:
Select * from A CodeNo 字段值 AA02 怎么会有两个?
2007-01-01 AB01 AA 10
2007-01-02 EA02 AA 15
2007-01-02 AF03 AA -12
2007-01-03 EG02 AA 20
2007-01-04 BH05 AA -18
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', -18Select
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_ CodeNo Code_ Num_01 Num_02 Num_03
2007-01-01 AB01 AA 10 0 10
2007-01-02 EA02 AA 15 0 25
2007-01-02 AF03 AA 0 -12 13
2007-01-03 EG02 AA 20 0 33
2007-01-04 BH05 AA 0 -18 15
*/
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', -18Select ID = Identity(Int, 1, 1), * Into #T From @ASelect
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 TDrop Table #T
--Result
/*
Date_ CodeNo Code_ Num_01 Num_02 Num_03
2007-01-01 AB01 AA 10 0 10
2007-01-02 EA02 AA 15 0 25
2007-01-02 AF03 AA 0 -12 13
2007-01-03 EG02 AA 20 0 33
2007-01-04 BH05 AA 0 -18 15
*/