用一句sql实现库存分配订单表 AA
订单号 订单明细行号 商品编号 销售数量
20150101 1 1001001 110
20150101 2 1001002 80
20150101 3 1001003 150
20150102 1 1001001 70
20150102 2 1001002 113
20150103 1 1001001 90
20150102 2 1001003 170
库存表 货位号 商品编号 库存数量 A1-1 1001001 30
A1-2 1001001 80
B1-1 1001001 110
A1-3 1001002 90
A1-4 1001002 110
A1-5 1001003 200
A1-6 1001003 150
订单号 订单明细行号 商品编号 销售数量
20150101 1 1001001 110
20150101 2 1001002 80
20150101 3 1001003 150
20150102 1 1001001 70
20150102 2 1001002 113
20150103 1 1001001 90
20150102 2 1001003 170
库存表 货位号 商品编号 库存数量 A1-1 1001001 30
A1-2 1001001 80
B1-1 1001001 110
A1-3 1001002 90
A1-4 1001002 110
A1-5 1001003 200
A1-6 1001003 150
因此,建议,结合你给的数据,贴一下你想的结果长什么样子的,还有你的SQL版本是多少?
订单号 订单明细行号 商品编号 销售数量 分配货位 分配库存数
20150101 1 1001001 110 A1-1 30
20150101 1 1001001 110 A1-2 80
20150101 2 1001002 80 A1-3 80
20150101 3 1001003 150 A1-5 150
20150102 1 1001001 70 B1-1 40
20150102 2 1001002 113 A1-3 10
20150102 2 1001002 113 A1-4 103
20150103 1 1001001 90 分配失败
20150103 2 1001003 170 A1-5 170剩余库存数为
货位号 商品编号 库存数量
A1-5 1001003 30
A1 1001002 7
SELECT ROW_NUMBER()
OVER(PARTITION BY 商品编号 ORDER BY 订单号)RN1
,*
FROM 订单表
)
,T2 AS(
SELECT ROW_NUMBER()
OVER(PARTITION BY 商品编号 ORDER BY GETDATE())RN2
,*
FROM 库存表
)
,CTE AS(
SELECT T1.订单号,T1.订单明细行号,T1.商品编号,T1.销售数量
,T2.货位号[分配货位],CASE WHEN T1.销售数量>T2.库存数量 THEN T2.库存数量 ELSE T1.销售数量 END[分配库存数]
,T1.RN1,T2.RN2,T2.库存数量-T1.销售数量[Balance]
--,CASE WHEN T1.销售数量>T2.库存数量 THEN 2 WHEN T1.销售数量<T2.库存数量 THEN 1 ELSE 0 END[NextType]
FROM T1 JOIN T2 ON T1.商品编号=T2.商品编号 AND T1.RN1=1 AND T2.RN2=1
UNION ALL
SELECT T1.订单号,T1.订单明细行号,T1.商品编号,T1.销售数量
,T2.[分配货位],CASE WHEN T1.销售数量>T2.[Balance] THEN T2.[Balance] ELSE T1.销售数量 END
,T1.RN1,T2.RN2,T2.[Balance]-T1.销售数量
FROM T1 JOIN CTE T2 ON T1.商品编号=T2.商品编号 AND T2.[Balance]>0 AND T1.RN1=T2.RN1+1
UNION ALL
SELECT T1.订单号,T1.订单明细行号,T1.商品编号,T1.销售数量
,T2.货位号,CASE WHEN -T1.[Balance]>T2.库存数量 THEN T2.库存数量 ELSE -T1.[Balance] END
,T1.RN1,T2.RN2,T2.库存数量+T1.[Balance]
FROM CTE T1 JOIN T2 ON T1.商品编号=T2.商品编号 AND T1.[Balance]<0 AND T2.RN2=T1.RN2+1
UNION ALL
SELECT T1.订单号,T1.订单明细行号,T1.商品编号,T1.销售数量
,T2.货位号,CASE WHEN T1.销售数量>T2.库存数量 THEN T2.库存数量 ELSE T1.销售数量 END
,T1.RN1,T2.RN2,T2.库存数量-T1.销售数量
FROM CTE T0 JOIN T1 ON T0.商品编号=T1.商品编号 AND T0.[Balance]=0 AND T1.RN1=T0.RN1+1
JOIN T2 ON T1.商品编号=T2.商品编号 AND T2.RN2=T0.RN2+1
)
SELECT 订单号,订单明细行号,商品编号,销售数量,[分配货位],[分配库存数]
FROM CTE
ORDER BY 订单号,订单明细行号,RN2大概分配结果是这样的,可以根据你的需要再做调整