一个sql语句
a0188 a_id c95903 c9590b c95904
1413 1 6.00 1 0.00
1413 2 7.00 0 0.00
1413 3 2.00 0 0.00
1413 4 0.50 1 0.00
实现下面的效果
第一条记录的值为6.00
最后一条记录的更新c95904的值为7.5,
也就是c9590b的值为1才更新,否则不更新
a0188 a_id c95903 c9590b c95904
1413 1 6.00 1 0.00
1413 2 7.00 0 0.00
1413 3 2.00 0 0.00
1413 4 0.50 1 0.00
实现下面的效果
第一条记录的值为6.00
最后一条记录的更新c95904的值为7.5,
也就是c9590b的值为1才更新,否则不更新
a0188 a_id c95903 c9590b c95904
1413 1 6.00 1 0.00
1413 2 7.00 0 0.00
1413 3 2.00 0 0.00
1413 4 0.50 1 0.00
实现下面的效果
第一条记录的值为6.00
最后一条记录的更新c95904的值为9.5,
也就是c9590b的值为1才更新,否则不更新
不好意思打错了。
if object_id('[test]') is not null
drop table [test]
create table [test](
[a0188] int,
[a_id] int,
[c95903] numeric(10,2),
[c9590b] int,
[c95904] numeric(10,2)
)
insert [test]
select 1413,1,6.00,1,0.00 union all
select 1413,2,7.00,0,0.00 union all
select 1413,3,2.00,0,0.00 union all
select 1413,4,0.50,1,0.00 union all
select 1413,5,6.00,0,0.00 union all
select 1413,6,7.00,0,0.00 union all
select 1413,7,0.50,1,0.00
update [test]
set [c95904]=t.[c95904] from(
select [a0188],[a_id],[c95904]=isnull(
(select SUM([c95903]) from test b where b.[a_id]<=a.[a_id]
and b.a_id>(select MAX(a_id) from test c where c.c9590b=1 and c.a_id<a.a_id)),a.[c95903])
from test a where [c9590b]=1
group by [a0188],[a_id],a.[c95903])t
where [test].a_id=t.a_id and [test].a0188=t.a0188select * from test/*
a0188 a_id c95903 c9590b c95904
1413 1 6.00 1 6.00
1413 2 7.00 0 0.00
1413 3 2.00 0 0.00
1413 4 0.50 1 9.50
1413 5 6.00 0 0.00
1413 6 7.00 0 0.00
1413 7 0.50 1 13.50
*/