34,590
社区成员
发帖
与我相关
我的任务
分享
declare @tb table(jh varchar(10), xjxh int, tgmc varchar(10), tggc int , xs decimal(18,2))
insert into @tb values('BK', 0, 'a', 5 , 5.00 )
insert into @tb values('BK', 1, 'a', 6 , 5.00 )
insert into @tb values('BK', 2, 'a', 8 , 5.00 )
insert into @tb values('BK', 3, 'a', 10 , 5.00 )
insert into @tb values('BK', 5, 'a', 12 , 5.00 )
--更新
update @tb set xs=(select sum(tggc) from @tb where jh=t.jh and xjxh<=t.xjxh) from @tb t
--查询
select * From @tb
/*
BK 0 a 5 5.00
Bk 1 a 6 11.00
BK 2 a 8 19.00
BK 3 a 10 29.00
Bk 5 a 12 41.00
*/
create table tb(jh varchar(10), xjxh int, tgmc varchar(10), tggc int , xs decimal(18,2))
insert into tb values('BK', 0, 'a', 5 , 5.00 )
insert into tb values('BK', 1, 'a', 5 , 5.00 )
insert into tb values('BK', 2, 'a', 5 , 5.00 )
insert into tb values('BK', 3, 'a', 5 , 5.00 )
insert into tb values('BK', 5, 'a', 5 , 5.00 )
go
--查询
select jh,xjxh,tgmc,xs = (select sum(xs) from tb where jh = t.jh and xjxh <= t.xjxh) from tb t
/*
jh xjxh tgmc xs
---------- ----------- ---------- ---------------------------------------
BK 0 a 5.00
BK 1 a 10.00
BK 2 a 15.00
BK 3 a 20.00
BK 5 a 25.00
(5 行受影响)
*/
--更改
update tb set xs = (select sum(xs) from tb where jh = t.jh and xjxh <= t.xjxh) from tb t
select * from tb
/*
jh xjxh tgmc tggc xs
---------- ----------- ---------- ----------- ---------------------------------------
BK 0 a 5 5.00
BK 1 a 5 10.00
BK 2 a 5 15.00
BK 3 a 5 20.00
BK 5 a 5 25.00
(5 行受影响)
*/
drop table tb