先进先出的订单分配处理

物料 批号 库存数
aa p01 5
aa p02 10
bb p01 20

订单 物料 订货数
1 aa 11
1 bb 10
2 aa 2
3 aa 1

要得到：

订单 物料 批号 出库
1 aa p01 5
1 aa p02 6
1 bb p01 10
2 aa p02 2
3 aa p02 3

要求：
使用update，不用函数、子查询、游标、循环，一句搞定。

------------------------------------------------------------------------------------------------------

--大致的思路
--测试数据
create table ta(物料 varchar(10),批号 varchar(10),库存数 int)
insert ta select 'aa','p01',5
union all select 'aa','p02',10
union all select 'aa','p03',20
union all select 'bb','p01',2
union all select 'bb','p02',20
create table tb(订单 int,物料 varchar(10),订货数 int)
insert tb select 1,'aa',11
union all select 1,'bb',10
union all select 2,'aa',20
union all select 3,'aa',1
go
--生成临时表
select b.订单,b.物料,a.批号,a.库存数,b.订货数,出库=cast(0 as int)
from ta a,tb b
where a.物料=b.物料
order by b.物料,a.批号,b.订单
go
--删除测试
drop table ta,tb
--实现思路

/*--生成的临时表如下

订单  物料  批号 库存数 订货数  出库    
---- ----- ---- ------ ------ -----
1    aa    p01  5      11     5 --还有6未分配,那就要等下一个批号来处理
                                    --记下订单号1
2    aa    p01  5      20     0 --不再处理,因为未分配数>0
3    aa    p01  5      1      0 --不再处理,因为未分配数>0
1    aa    p02  10     11     6 --有了新库存10,与上次未分配的订单号比较,>=
                                     --所以进行分配,分配的结果是库存余4,未分配数
                                     --=0,记下本次订单号+1,如分配数>0,记本次订单号
2    aa    p02  10     20     4 --上次结余库存4,分配,余16未分配,记下订单号2
3    aa    p02  10     1      0 --不再处理,因为未分本数>0
1    aa    p03  20     11     0 --有新库存20,但订单号<上次记下的订单号,不处理
2    aa    p03  20     20     16     --满足条件,分配,余库库存4
3    aa    p03  20     1      1 --满足条件,分配
1    bb    p01  2      10     2 --物料变化变化,重新开始分配,未分配数8
                                     --记下订单号1
1    bb    p02  20     10     8 --有新库存,分配

（所影响的行数为 11 行）
--*/

--测试数据
create table ta(物料 varchar(10),批号 varchar(10),库存数 int)
--/*--第一套测试数据
insert ta select 'aa','p01',5
union all select 'aa','p02',10
union all select 'bb','p01',20
--*/

/*--第二套测试数据
insert ta select 'aa','p01',5
union all select 'aa','p02',10
union all select 'aa','p03',20
union all select 'bb','p01',2
union all select 'bb','p02',20
--*/

/*--第三套测试数据
insert ta select 'aa','p01',50
union all select 'aa','p02',10
union all select 'bb','p01',20
--*/

/*--第四套测试数据
insert ta select 'aa','p01',50
union all select 'aa','p02',8
union all select 'aa','p03',6
union all select 'aa','p04',3
union all select 'bb','p01',2
union all select 'bb','p02',20
--*/

create table tb(订单 int,物料 varchar(10),订货数 int)
--/*--第一套测试数据
insert tb select 1,'aa',11
union all select 1,'bb',10
union all select 2,'aa',2
union all select 3,'aa',1
--*/

/*--第二套测试数据
insert tb select 1,'aa',11
union all select 1,'bb',10
union all select 2,'aa',20
union all select 3,'aa',1
--*/

/*--第三套测试数据
insert tb select 1,'aa',11
union all select 1,'bb',10
union all select 2,'aa',2
union all select 3,'aa',1
--*/

/*--第四套测试数据
insert tb select 1,'aa',7
union all select 1,'bb',10
union all select 2,'aa',8
union all select 2,'bb',8
union all select 3,'aa',1
--*/
go

select b.订单,b.物料,a.批号,a.库存数,b.订货数,出库=cast(0 as int)
 ,未分配=cast(null as int),库存结余=cast(null as int),未配订单=cast(null as int)
into #t
from ta a,tb b
where a.物料=b.物料
order by b.物料,a.批号,b.订单


select * from #t

-------------##############   先进先出计算后	starts ##############----------------
declare @物料 varchar(10),@批号 varchar(10),@订单 int
declare @出库 int,@库存 int,@未分配 int

update 
	#t 
set 
	@库存=case 
			when 物料=@物料 then 
				case 
					when 批号=@批号 then @库存 
				else 
					@库存+库存数 
				end
		  else 
			库存数  
		  end,
	@出库=case 
			when @库存>0 then 
				case when @物料=物料 then 
					case when isnull(@订单,订单)<=订单 then 
						case when @未分配>0 then 
							case 
								when @库存<@未分配 then @库存 
								else @未分配 
							end
						else 
							case when @库存<订货数 then @库存 
							else 
								订货数 
							end 
						end
					else 
						0 
					end
				else 
					case 
						when @库存<订货数 then @库存 
					else 
						订货数 
					end 
				end
		   else 
			0 
		   end,
	@未分配=case 
				when @出库>0 then 
					case 
						when @物料=物料 and @未分配>0 then @未分配-@出库
					else 
						订货数-@出库 
					end
				else 
					@未分配 
				end,
	 @订单=case 
				when @物料=物料 then 
					case when @出库>0 then 
						case 
							when @未分配>0 then 订单 
						else 
							订单+1 
						end
					else 
						@订单 
					end
		  else 
				case 
					when @未分配>0 then 订单 
				else 
					订单+1 
				end 
		  end,
 @库存=@库存-@出库,
 出库=@出库,
 未分配=@未分配,
 库存结余=@库存,
 未配订单=@订单,
 @物料=物料,
 @批号=批号
 -------------##############  先进先出计算后 ends ##############-------------------

--##############显示更新结果##############--
select 订单,物料,批号,出库 from #t
where 出库>0
order by 订单,物料,批号
go

----------------##############----------------------
select * from #t
where 出库>0
order by 订单,物料,批号

/*--测试结果

订单          物料         批号         出库          
----------- ---------- ---------- ----------- 
1           aa         p01        5
1           aa         p02        6
1           bb         p01        10
2           aa         p02        2
3           aa         p02        1

（所影响的行数为 5 行）
--*/

/*---一些说明:

处理语句中的下述这些,只是为了调试时,方便观察用的,没有实际处理用处:
未分配=@未分配,库存结余=@库存,未配订单=@订单,

同样的,生成临时表中的下述这些也只是调试用的(实际处理不需要):
,未分配=cast(null as int),库存结余=cast(null as int),未配订单=cast(null as int)
--*/