create table list(id varchar(03), name varchar(10), order_qty int, input int, x varchar(01))
insert into list select '001','bb',200,0,'c'
insert into list select '002','aa',100,0,'b'
insert into list select '003','aa',200,0,'c'
insert into list select '004','cc',200,0,'c'GOdeclare @name varchar(20)
set @name='aa'
declare @input int
set @input=150Update list
set input= case when (select sum(order_qty) from list A where A.name=@name and A.id<=list.id)<=@input
then order_qty
else case when isnull((select sum(order_qty) from list A where A.name=@name and A.id<list.id),0)<=@input
then @input-isnull((select sum(order_qty) from list A where A.name=@name and A.id<list.id),0)
else 0 end
end
where name=@name
GOselect * from list
GO
/*
id name order_qty input x
---- ---------- ----------- ----------- ----
001 bb 200 0 c
002 aa 100 100 b
003 aa 200 50 c
004 cc 200 0 c*/
drop table list这个语句是怎么实现update的。
select sum(order_qty) from list A where A.name=@name and A.id<=list.id
这句是计算总数量的还是什么啊?
在说update的时候没有指定ID啊?
算一下name相同时,id比当前id小或等于的order_qty的总和.
id value
1 10
2 20
3 30
4 40那个select sum()就是算累加值,结果
1 10
2 30
3 60
4 100然后去和 @input 比,假如@input是50好了
id=1 时,case when 10<=50 所以update=10
id=2 时,case when 30<=50 所以update=30
id=3 时,case when 60>50了,所以跳到下一层case when 中,因为id<3的时候,sum是30<=50,所以用@input-30=20
id=4 时,在第2层的case when 中,因为id<4时的sum是60>50,所以是0这么解释不晓得明白没?