create table t(id int,num int)
insert into t select 1,200
insert into t select 2,10
insert into t select 3,60
insert into t select 4,80
insert into t select 5,600
delete from t
declare @sum int
set @sum=300
update a
set
num=(case when (select sum(num) from t where id<=a.id)< @sum
then 0
when
(select sum(num) from t where id< a.id)< @sum
and
(select sum(num) from t where id<=a.id)>=@sum
then
(select sum(num) from t where id<=a.id)-@sum
else a.num end)
from
t a
select * from t
/*
id num
----------- -----------
1 0
2 0
3 0
4 50
5 600
*/
虽然很普通,但让人启发很大
觉得写的比较好的语句(UPDTAE)
最新推荐文章于 2022-09-19 11:22:29 发布