有表
a1 a2 a3 a4
A 1 4
A 2 5
A 3 7
A 4 9
B 1 3
B 2 6
B 3 4
B 4 8
B 5 11现在想这样,在a1相同的情况下,a2按顺序排好,a4等于自己的a3加上前一项的a3(第一个a4无前一项,就只能是自己的a3了)
如下:
a1 a2 a3 a4
A 1 4 4
A 2 5 4+5 = 9
A 3 7 5+7 = 12
A 4 9 7+9 = 16
B 1 3 3
B 2 6 3+6 = 9
B 3 4 6+4 = 10
B 4 8 4+8 = 12
B 5 11 8+11 = 19问代码如何,急,谢谢!
a1 a2 a3 a4
A 1 4
A 2 5
A 3 7
A 4 9
B 1 3
B 2 6
B 3 4
B 4 8
B 5 11现在想这样,在a1相同的情况下,a2按顺序排好,a4等于自己的a3加上前一项的a3(第一个a4无前一项,就只能是自己的a3了)
如下:
a1 a2 a3 a4
A 1 4 4
A 2 5 4+5 = 9
A 3 7 5+7 = 12
A 4 9 7+9 = 16
B 1 3 3
B 2 6 3+6 = 9
B 3 4 6+4 = 10
B 4 8 4+8 = 12
B 5 11 8+11 = 19问代码如何,急,谢谢!
from taselect a1,a2,a3,a4 = a4 + isnull(b.a4,0)
from # a,#b
where a.px = b.px - 1 and a.a1 = b.a1
declare @t table(a1 varchar(10),a2 int,a3 int,a4 int)
insert into @t select 'A',1,4,0
insert into @t select 'A',2,5,0
insert into @t select 'A',3,7,0
insert into @t select 'A',4,9,0
insert into @t select 'B',1,3,0
insert into @t select 'B',2,6,0
insert into @t select 'B',3,4,0
insert into @t select 'B',4,8,0
insert into @t select 'B',5,11,0select a1,a2,a3,
a4=(select sum(a3) from @t where a1=a.a1 and a2 in(a.a2,a.a2-1))
from @t a
declare @t table(a1 varchar(10),a2 int,a3 int,a4 int)
insert into @t select 'A',1,4,0
insert into @t select 'A',2,5,0
insert into @t select 'A',3,7,0
insert into @t select 'A',4,9,0
insert into @t select 'B',1,3,0
insert into @t select 'B',2,6,0
insert into @t select 'B',3,4,0
insert into @t select 'B',4,8,0
insert into @t select 'B',5,11,0
select a.a1,a.a2,a.a3,a4 = a.a3 + isnull(b.a3,0)
from @t a left join @t b
on a.a2 = b.a2 + 1
and a.a1 = b.a1