例如这个表id num
1 1
2 0
3 0
4 0
5 0
6 0现在的问题是先要把id1的num值平方,然后加上id2,得出值填入id2的num中,具体就是下表id num
1 1
2 3
3 12
4 148
。这个语句应该怎么写啊,虚心求教,我想过用游标,可惜太慢了
1 1
2 0
3 0
4 0
5 0
6 0现在的问题是先要把id1的num值平方,然后加上id2,得出值填入id2的num中,具体就是下表id num
1 1
2 3
3 12
4 148
。这个语句应该怎么写啊,虚心求教,我想过用游标,可惜太慢了
insert into @t select 1,1
insert into @t select 2,0
insert into @t select 3,0
insert into @t select 4,0
insert into @t select 5,0
insert into @t select 6,0declare @sum int
set @sum=0update @t set @sum=@sum*@sum+num,num=@sumselect * from @t
index id num
a 1 3
a 2 0
a 3 0
a 4 0
b 1 4
b 2 0
b 3 0
b 4 0按照要求的话是按照index分组更新,这个语句又该如何,虚心求教
declare @t table([index] varchar(10),id int,num int)
insert @t
select 'a',1,3 union all
select 'a',2,0 union all
select 'a',3,0 union all
select 'a',4,0 union all
select 'b',1,4 union all
select 'b',2,0 union all
select 'b',3,0 union all
select 'b',4,0declare @sum int
set @sum=0UPDATE @t set @sum=
case
when not exists(select 1 from @t where [index] = a.[index] and id < a.id)
then num /*当是每组的第一行时初始化@num*/
else @sum*@sum+id
end,
num=@sum
FROM @t aselect * from @t