按需求先进先出分配库存

--测试数据
create table #库存(
wareid varchar(13),
makeno varchar(40),
storeqty numeric(16,6))

insert into #库存 select 'A','批号1',30
insert into #库存 select 'A','批号2',40
insert into #库存 select 'A','批号3',50
insert into #库存 select 'B','批号8',15
insert into #库存 select 'B','批号9',40
insert into #库存 select 'B','批号12',1000
insert into #库存 select 'C','批号1',60
insert into #库存 select 'C','批号4',120

create table #需求(
wareid varchar(13),
applyqty numeric(16,6)) 

insert into #需求 select 'A',80
insert into #需求 select 'B',50
insert into #需求 select 'C',180


create table #分配(
wareid varchar(13),
makeno varchar(40),
分配数量 numeric(16,6))


select * from #库存
select * from #需求
select * from #分配

--排序取row_number
select *,row_number() over(partition by wareid order by wareid,len(makeno),makeno) as num