将一个数量在一组记录中按照先后顺序进行分配

Question

declare @t1 table(SEQ  int,[GROUP] varchar(10),Need_Qty int,Ass_Qty int)
insert into @t1 select 1,'A',50,null
insert into @t1 select 2,'A',20,null
insert into @t1 select 3,'A',30,null
insert into @t1 select 4,'B',100,null
insert into @t1 select 5,'B',20,null
insert into @t1 select 6,'B',30,null
insert into @t1 select 7,'C',50,null
insert into @t1 select 8,'C',60,nulldeclare @t2 table([GROUP] varchar(10),Ass_Qty int)
insert into @t2 select 'A',80
insert into @t2 select 'B',130
insert into @t2 select 'C',50
update a
set
    a.Ass_Qty=(case 
                 when (select sum(Need_Qty) from @t1 where [GROUP]=a.[GROUP] and SEQ<=a.SEQ)<=b.Ass_Qty
                     then a.Need_Qty
                 when isnull((select sum(Need_Qty) from @t1 where [GROUP]=a.[GROUP] and SEQ<a.SEQ),0)<b.Ass_Qty
                     then b.Ass_Qty-isnull((select sum(Need_Qty) from @t1 where [GROUP]=a.[GROUP] and SEQ<a.SEQ),0)
                 else
                     0
               end)
from
    @t1 a,
    @t2 b
where
    a.[GROUP]=b.[GROUP]select * from @t1/*SEQ         GROUP      Need_Qty    Ass_Qty     
----------- ---------- ----------- ----------- 
1           A          50          50
2           A          20          20
3           A          30          10
4           B          100         100
5           B          20          20
6           B          30          10
7           C          50          50
8           C          60          0
*/

Answer 1

--try
create function f_num(@group char(1)
as
returns int
begin
declare @num int
select @num=ass_qty from tb where [group]=@group
return @num
end
go
declare @total int,@group char(1)
select @total=0,@group=''
update ta 
set ass_qty=case when @total<=dbo.f_num([group]) then need_qty
when @total>dbo.f_num([group]) and @total-need_qty<dbo.f_num([group]) 
then dbo.f_num([group])-(@total-need_qty) else 0 end,
@total=case when [group]<>@group then need_qty else @total+need_qty end

Answer 2

declare @t table(SEQ int,[GROUP] char(1),Need_Qty int)
insert @t select 1,'A',50 union all
 select 2,'A',20 union all
 select 3,'A',30 union all
 select 4,'B',100 union all
 select 5,'B',20 union all
 select 6,'B',30 union all
 select 7,'C',50 union all
 select 8,'C',60declare @t1 table([GROUP] char(1), Ass_Qty int)
insert @t1 select 'A',80 union all
select 'B',130 union all
select 'C',50   select seq,[group],a.need_qty,
case when (select Ass_Qty from @t1 where [group] = a.[group])
 - (select sum(need_qty) from @t where a.[group] = [group] and SEQ <= a.SEQ group by [group])> 0 
then a.need_qty else (select Ass_Qty from @t1 where [group] = a.[group])
 - isnull((select sum(need_qty) from @t where a.[group] = [group] and SEQ < a.SEQ group by [group]),0) end 
as Ass_Qty
from @t a/*seq         group need_qty    Ass_Qty     
----------- ----- ----------- ----------- 
1           A     50          50
2           A     20          20
3           A     30          10
4           B     100         100
5           B     20          20
6           B     30          10
7           C     50          50
8           C     60          0
*/

Answer 3

set nocount on
create table ta(SEQ  int,[GROUP] char(1),Need_Qty int,Ass_Qty int)
insert into ta select 1,'A',50,null
insert into ta select 2,'A',20,null
insert into ta select 3,'A',30,null
insert into ta select 4,'B',100,null
insert into ta select 5,'B',20,null
insert into ta select 6,'B',30,null
insert into ta select 7,'C',50,null
insert into ta select 8,'C',60,nullcreate table tb([GROUP] char(1),Ass_Qty int)
insert into tb select 'A',80
insert into tb select 'B',130
insert into tb select 'C',50
go
create function f_num(@group char(1))
returns int
as
begin
declare @num int
select @num=ass_qty from tb where [group]=@group
return @num
end
go
declare @total int,@group char(1)
select @total=0,@group=''
update ta 
set @total=case when [group]<>@group then need_qty else @total+need_qty end,
@group=[group],
ass_qty=case when @total<=dbo.f_num([group]) then need_qty
when @total>dbo.f_num([group]) and @total-need_qty<dbo.f_num([group]) 
then dbo.f_num([group])-(@total-need_qty) else 0 endselect * from tadrop table ta,tb
drop function dbo.f_num

Answer 4

declare @t table(SEQ int,[GROUP] char(1),Need_Qty int)
insert @t select 1,'A',50 union all
 select 2,'A',20 union all
 select 3,'A',30 union all
 select 4,'B',100 union all
 select 5,'B',20 union all
 select 6,'B',30 union all
 select 7,'C',50 union all
 select 8,'C',60declare @t1 table([GROUP] char(1), Ass_Qty int)
insert @t1 select 'A',80 union all
select 'B',130 union all
select 'C',50   select a.seq,a.[group],a.need_qty,
case when b.Ass_Qty
 - (select sum(need_qty) from @t where a.[group] = [group] and SEQ <= a.SEQ group by [group])> 0 
then a.need_qty else b.Ass_Qty
 - isnull((select sum(need_qty) from @t where a.[group] = [group] and SEQ < a.SEQ group by [group]),0) end 
as Ass_Qty
from @t a,@t1 b
where a.[group] =b.[group]/*seq         group need_qty    Ass_Qty     
----------- ----- ----------- ----------- 
1           A     50          50
2           A     20          20
3           A     30          10
4           B     100         100
5           B     20          20
6           B     30          10
7           C     50          50
8           C     60          0
*/