满足条件后,按时间先后进行出库:库存表:时间 数量 批次 商品ID
2002.1.1 40 001 SKU1 2003.1.1 100 001 SKU1
2003.1.1 90 004 SKU1 2003.1.5 200 002 SKU2 2003.1.8 150 003 SKU3 订单信息:订单号 数量 批次 商品ID Out01 60 001 SKU1 Out01 30 001 SKU1Out01 70 002 SKU2 Out01 170 003 SKU3 ----------------------------------------------------------------
*********结果如下三表**********库存表:时间 数量 批次 商品ID
2002.1.1 0 001 SKU1
2003.1.1 80 001 SKU1
2003.1.1 30 004 SKU1 2003.1.5 70 002 SKU2 2003.1.8 0 003 SKU3
出库明细表订单号 时间 数量 批次 商品ID
Out01 2002.1.1 40 001 SKU1 Out01 2003.1.1 20 001 SKU1
Out01 2003.1.1 30 004 SKU1 Out01 2003.1.5 70 002 SKU2 Out01 2003.1.8 150 003 SKU3
不足明细:订单号 数量 批次 商品ID Out01 20 003 SKU3
2002.1.1 40 001 SKU1 2003.1.1 100 001 SKU1
2003.1.1 90 004 SKU1 2003.1.5 200 002 SKU2 2003.1.8 150 003 SKU3 订单信息:订单号 数量 批次 商品ID Out01 60 001 SKU1 Out01 30 001 SKU1Out01 70 002 SKU2 Out01 170 003 SKU3 ----------------------------------------------------------------
*********结果如下三表**********库存表:时间 数量 批次 商品ID
2002.1.1 0 001 SKU1
2003.1.1 80 001 SKU1
2003.1.1 30 004 SKU1 2003.1.5 70 002 SKU2 2003.1.8 0 003 SKU3
出库明细表订单号 时间 数量 批次 商品ID
Out01 2002.1.1 40 001 SKU1 Out01 2003.1.1 20 001 SKU1
Out01 2003.1.1 30 004 SKU1 Out01 2003.1.5 70 002 SKU2 Out01 2003.1.8 150 003 SKU3
不足明细:订单号 数量 批次 商品ID Out01 20 003 SKU3
貼太多,沒人會每天關注同一貼.use Tempdb
go
--> -->
if not object_id(N'Stock') is null
drop table Stock
Go
Create table Stock([时间] Datetime,[数量] int,[批次] nvarchar(3),[商品ID] nvarchar(4))
Insert Stock
select '2002.1.1',40,N'001',N'SKU1' union all
select '2003.1.1',100,N'001',N'SKU1' union all
select '2003.1.1',90,N'004',N'SKU1' union all
select '2003.1.5',200,N'002',N'SKU2' union all
select '2003.1.8',150,N'003',N'SKU3'
Go
if not object_id(N'SODetail') is null
drop table SODetail
Go
Create table SODetail([订单号] nvarchar(5),[数量] int,[批次] nvarchar(3),[商品ID] nvarchar(4))
Insert SODetail
select N'Out01',60,N'001',N'SKU1' union all
select N'Out01',30,N'001',N'SKU1' union all
select N'Out01',70,N'002',N'SKU2' union all
select N'Out01',170,N'003',N'SKU3'
Go
if object_id('Tempdb..#SODetail') is not null
drop table #SODetail
select ID=Identity(int,1,1),* into #SODetail from SODetail--需要一個大小排序select
b.时间,
[数量]=b.[数量]-(case when b.Qty>a.Qty then a.Qty else b.Qty end-case when b.Qty-b.[数量]>a.Qty-a.[数量] then b.Qty-b.[数量] else a.Qty-a.[数量] end),
b.批次,b.商品ID
from
(select *,Qty=(select sum([数量]) from #SODetail where [商品ID]=a.[商品ID] and [批次]=a.[批次] and ID<=a.ID) from #SODetail a)a,
(select *,Qty=(select sum([数量]) from Stock where [商品ID]=a.[商品ID] and [批次]=a.[批次] and [时间]<=a.[时间]) from Stock a)b
where a.[商品ID]=b.[商品ID] and a.[批次]=b.[批次] and b.Qty>a.Qty-a.[数量] and a.Qty>b.Qty-b.[数量]
select
b.时间,
[数量]=case when b.Qty>a.Qty then a.Qty else b.Qty end-case when b.Qty-b.[数量]>a.Qty-a.[数量] then b.Qty-b.[数量] else a.Qty-a.[数量] end,
b.批次,b.商品ID
from
(select *,Qty=(select sum([数量]) from #SODetail where [商品ID]=a.[商品ID] and [批次]=a.[批次] and ID<=a.ID) from #SODetail a)a,
(select *,Qty=(select sum([数量]) from Stock where [商品ID]=a.[商品ID] and [批次]=a.[批次] and [时间]<=a.[时间]) from Stock a)b
where a.[商品ID]=b.[商品ID] and a.[批次]=b.[批次] and b.Qty>a.Qty-a.[数量] and a.Qty>b.Qty-b.[数量]
select
b.时间,
[数量]=abs(case when b.Qty>a.Qty then a.Qty else b.Qty end-case when b.Qty-b.[数量]>a.Qty-a.[数量] then b.Qty-b.[数量] else a.Qty-a.[数量] end),
b.批次,b.商品ID
from
(select *,Qty=(select sum([数量]) from #SODetail where [商品ID]=a.[商品ID] and [批次]=a.[批次] and ID<=a.ID) from #SODetail a)a,
(select *,Qty=(select sum([数量]) from Stock where [商品ID]=a.[商品ID] and [批次]=a.[批次] and [时间]<=a.[时间]) from Stock a)b
where a.[商品ID]=b.[商品ID] and a.[批次]=b.[批次] and not (b.Qty>a.Qty-a.[数量] and a.Qty>b.Qty-b.[数量])