--想到就随性， 随手写的，没考虑效率等，应该有更好的算法
 
create  table T (ItemType varchar(3),Item varchar(3),Amount  numeric(18,2))
insert into T
select 'A','A1',10 union all
select 'A','A2',30 union all
select 'A','A3',60 union all
select 'SUM',null,'100' union all
select 'B','B1',20 union all
select 'B','B2',80 union all
select 'SUM',null,'100' union all
select 'C','C1',40 union all
select 'C','C2',60 union all
select 'SUM',null,'100' union all
select 'D','D1',30 union all
select 'D','D2',70 union all
select 'SUM',null,'100'


GO

Create function dbo.test(@a numeric(18,2),@b numeric(18,2),@c numeric(18,2),@d numeric(18,2))
returns  numeric(18,2)
as
begin
 declare @re numeric(18,2)
 select @re=min(num)
 from ( 
         select @a as num
         union 
         select @b
         union 
         select @c
         union 
         select @d
            ) A

return @re
end

GO
Create Proc  dbo.get_test 
AS

select  id=identity(int,1,1), Item,Amount, Amount as leave
into #A
from T where ItemType='A' order by Item

select  id=identity(int,1,1), Item,Amount, Amount  as leave
into #B
from T where ItemType='B' order by Item

select  id=identity(int,1,1), Item,Amount, Amount  as leave
into #C
from T where ItemType='C' order by Item

select  id=identity(int,1,1), Item,Amount, Amount as leave
into #D
from T where ItemType='D' order by Item

declare @sum numeric(18,2)
set @sum=(select top 1 Amount  from T where ItemType='SUM')

declare @table table( A varchar(03), B varchar(03), C varchar(03), D varchar(03), amount numeric(18,2))


declare  @ida int,@idb int,@idc int,@idd int , @min numeric(18,2)
declare  @itema varchar(03), @itemb varchar(03), @itemc varchar(03), @itemd varchar(03)
declare @a numeric(18,2),@b numeric(18,2),@c numeric(18,2),@d numeric(18,2),@active numeric(18,2)
set @active=0

while @active<@sum
begin
    select  @ida=id,@itema=item,@a=leave from #A     where id=(select min(id) from #A where leave>0)
    select  @idb=id,@itemb=item,@b=leave from #B     where id=(select min(id) from #B where leave>0)
    select  @idc=id,@itemc=item,@c=leave from #C     where id=(select min(id) from #C where leave>0)
    select  @idd=id,@itemd=item,@d=leave from #D     where id=(select min(id) from #D where leave>0)

    select @min= dbo.test(@a,@b,@c,@d)

    Insert into @table  select  @itema,@itemb,@itemc,@itemd,@min

    Update #A set leave=leave-@min  where id=@ida
    Update #B set leave=leave-@min  where id=@idb
    Update #C set leave=leave-@min where id=@idc
    Update #D set leave=leave-@min where id=@idd

    select @active=sum(Amount) from @table
end

select * from @table

drop table #A,#B,#C,#D

GO

--驗證結果
Exec    get_test
/*
A    B    C    D    amount               
---- ---- ---- ---- -------------------- 
A1   B1   C1   D1   10.00
A2   B1   C1   D1   10.00
A2   B2   C1   D1   10.00
A2   B2   C1   D2   10.00
A3   B2   C2   D2   60.00
*/

GO
drop table T

drop function dbo.test

drop proc get_test

------解决方案--------------------

SQL code
create table t1(TYPE varchar(10),ITEM varchar(10),AMOUNT money)
insert into T1 select 'A','A1',10 
insert into T1 select 'A','A2',30 
insert into T1 sele