先进先出的订单分配处理

原帖地址:

http://community.csdn.net/Expert/topic/3239/3239774.xml?temp=9.711856E-02

物料 批号 库存数
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.订单

--生成出库数据
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,
 @库存=@库存-@出库,
 出库=@出库,
 
 未分配=@未分配,库存结余=@库存,未配订单=@订单,
 @物料=物料,@批号=批号

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

--删除测试
drop table ta,tb
drop table #t

/*--测试结果

订单          物料         批号         出库         
----------- ---------- ---------- -----------
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)
--*/