一次修改多行，如何用sql语句来实现？

Question

表结构如下
id  number
1    20.0
2    30.0
3    40.0
给定一个数值30,要求从以上表里扣除这个数量，顺序是根据number，由小到大，依次扣除，先从number=20那条记录扣除，id=1的那条记录，number=0;剩余的10从id=2的那条记录扣除，id=2的记录，number=20;要求得到这样的结果
id  number
1    0
2    20.0
3    40.0

Answer 1

declare @tb table(id int,  number int)
declare @aa int 
set @aa=30
insert @tb select 1,    20
insert @tb select 2  ,  30 
insert @tb select 3 ,   40 
select  id,number=(
case when (select sum(number) from @tb where id<=a.id)<=@aa then 0 
     when (select sum(number) from @tb where id<=a.id-1)>@aa then number else (select sum(number) from @tb where id<=a.id)-@aa end ) from @tb  a/*
id          number      
----------- ----------- 
1           0
2           20
3           40（所影响的行数为 3 行）
*/

Answer 2

declare @tb table(id int,  number int)
declare @aa int 
set @aa=30
insert @tb select 1,    20
insert @tb select 2  ,  30 
insert @tb select 3 ,   40 
update  a set number=(
case when (select sum(number) from @tb where id<=a.id)<=@aa then 0 
     when (select sum(number) from @tb where id<=a.id-1)>@aa then number else (select sum(number) from @tb where id<=a.id)-@aa end ) 
from @tb  a
select * from @tb
/*id          number      
----------- ----------- 
1           0
2           20
3           40（所影响的行数为 3 行）
*/

Answer 3

设要扣的总数为x，本题等价于：
对于按值从小到大排列的记录，前n条记录之和y(n)大于等于x的最小n
序号等于n的值改为y(n)-x，序号小于n的值改为0

Answer 4

declare @i int ,@j int 
set @i =60
set @J = 1update ta
set number = case when @I >= 0 and @j>0 then 0 else number + @i end ,
    @j= case when @i > 0 then  1 else 0 end,
    @i= case when @i > 0 then @I - number else 0 end

Answer 5

------------------------------------
-- Author:flystone 
-- Version:V1.001  
-- Date:2009-08-06  
-------------------------------------- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(id int,number int)
Go
Insert into ta
select 1,20.0 union all
select 2,30.0 union all
select 3,40.0 
Go
--Start
declare @i int ,@j int 
set @i =30
set @J = 1update ta
set number = case when @I >= 0 and @j>0 then 0 else number + @i end ,
    @j= case when @i > 0 then  1 else 0 end,
    @i= case when @i > 0 then @I - number else 0 endselect * from ta
--Result:
/*id          number      
----------- ----------- 
1           0
2           10
3           40（所影响的行数为 3 行）*/
--End 

Answer 6

--> 测试数据: t
create table t(id int,number numeric(5,1))
insert into t
select 1,20.0 union all
select 2,30.0 union all
select 3,40.0
go
create proc wsp
@cost int         --销售量
as
--先得出该货物的库存是否够
declare @spare int --剩余库存
select @spare=sum(number) from t
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set number=
case when (select @cost-isnull(sum(number),0) from t where id<=a.id)>=0
then 0 
else
case  when (select @cost-isnull(sum(number),0) from t where id<a.id)>0 then
a.number-(select @cost-isnull(sum(number),0) from t where id<a.id)
else a.number end
end
from t a 
end
else
raiserror('库存不足',16,1)
return 
goexec wsp 30select * from t

Answer 7

 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,number NUMERIC(9,1))
INSERT INTO @T
SELECT 1,20.0 UNION ALL
SELECT 2,30.0 UNION ALL
SELECT 3,40.0 UNION ALL
SELECT 4,50.0--SQL查询如下:DECLARE @number NUMERIC(9,1),@p_number NUMERIC(9,1);
SELECT @number = 45;UPDATE @T SET
    number = CASE WHEN @p_number - number > 0
                     THEN 0
                  ELSE
                     CASE WHEN number - @p_number >=0 AND @p_number >= 0 
                             THEN number - @p_number
                          ELSE number END
             END,
    @p_number = @number,
    @number = @p_number - number 
SELECT * FROM @T;/*
id          number
----------- ---------------------------------------
1           0.0
2           5.0
3           40.0
4           50.0(4 行受影响)
*/

Answer 8

建议分多步执行!
楼上的例子都不具通用性,仅能支持lz所列的特例.用一while语句多次判断,扣除,直到扣完为止!