22,209
社区成员
发帖
与我相关
我的任务
分享求修改一个更新语句,各位牛哥来帮帮忙!
--库存表
CREATE TABLE [dbo].[WMS_Stock](
[Stock_ID] [BIGINT] IDENTITY(1,1) NOT NULL,
[StockDate] [DATETIME] NULL,
[StockSkuCode] [NVARCHAR](50) NULL,
[StockSkuName] [NVARCHAR](100) NULL,
[StockBatchNo] [NVARCHAR](50) NULL,
[SkuSta] [NVARCHAR](50) NULL,
[StockQty] [BIGINT] NULL,
[StockZQty] [BIGINT] NULL,
[StockLoca] [NVARCHAR](50) NULL
) ON [PRIMARY]
GO
--出库表
CREATE TABLE [dbo].[WMS_Out_OrderList](
[Order_ID] [BIGINT] IDENTITY(1,1) NOT NULL,
[OrderNO] [NVARCHAR](50) NULL,
[OrderListSkuCode] [NVARCHAR](50) NULL,
[OrderListSkuName] [NVARCHAR](100) NULL,
[OrderListBatchNo] [NVARCHAR](50) NULL,
[SkuSta] [NVARCHAR](50) NULL,
[OrderListQty] [BIGINT] NULL,
[OrderListTime] [DATETIME] NULL
) ON [PRIMARY]
GO
--插入库存表数据
insert WMS_Stock([Stock_ID],[StockDate],[StockSkuCode],[StockSkuName],[StockBatchNo],[SkuSta],[StockQty],[StockZQty],[StockLoca]) values (12,'2015-09-16 10:01:00.000','1001001','榛果威化巧克力T3（6*16*T3）','20140911','C001',7,7,'A1101')
insert WMS_Stock([Stock_ID],[StockDate],[StockSkuCode],[StockSkuName],[StockBatchNo],[SkuSta],[StockQty],[StockZQty],[StockLoca]) values (13,'2015-09-16 10:01:00.000','1001001','榛果威化巧克力T3（6*16*T3）','20140709','C001',13,13,'A1102')
insert WMS_Stock([Stock_ID],[StockDate],[StockSkuCode],[StockSkuName],[StockBatchNo],[SkuSta],[StockQty],[StockZQty],[StockLoca]) values (14,'2015-09-16 11:51:00.000','1001001','榛果威化巧克力T3（6*16*T3）','20141219','C001',4,0,'A1103')
insert WMS_Stock([Stock_ID],[StockDate],[StockSkuCode],[StockSkuName],[StockBatchNo],[SkuSta],[StockQty],[StockZQty],[StockLoca]) values (15,'2015-09-16 11:51:00.000','1001001','榛果威化巧克力T3（6*16*T3）','20131125','C001',37,5,'A1104')
--写入出库表数据
insert WMS_Out_OrderList([Order_ID],[OrderNO],[OrderListSkuCode],[OrderListSkuName],[OrderListBatchNo],[SkuSta],[OrderListQty],[OrderListTime]) values (1,'OGR201511120001','1001001','榛果威化巧克力T3（6*16*T3）','20140911','C001',10,'2015-11-12 11:51:00.000')
insert WMS_Out_OrderList([Order_ID],[OrderNO],[OrderListSkuCode],[OrderListSkuName],[OrderListBatchNo],[SkuSta],[OrderListQty],[OrderListTime]) values (2,'OGR201511120001','1001001','榛果威化巧克力T3（6*16*T3）','20140709','C001',15,'2015-11-12 11:51:00.000')
insert WMS_Out_OrderList([Order_ID],[OrderNO],[OrderListSkuCode],[OrderListSkuName],[OrderListBatchNo],[SkuSta],[OrderListQty],[OrderListTime]) values (3,'OGR201511120001','1001001','榛果威化巧克力T3（6*16*T3）','20131125','C001',5,'2015-11-12 11:51:00.000')
/*字段表中文解释
WMS_Stock表
Stock_ID ID自增
StockDate 创建时间
StockSkuCode 产品代码
StockSkuName 产品名称
StockBatchNo 批次号
SkuSta 状态
StockQty 库存数量
StockZQty 发货占用数量
StockLoca 库位
WMS_Out_OrderList表
Order_ID ID自增
OrderNO 出库单号
OrderListSkuCode 产品代码
OrderListSkuName 产品名称
OrderListBatchNo 批次号
SkuSta 状态
OrderListQty 出库数量
OrderListTime 创建时间 */
--问题根据产品代码,批次号,状态条件用出库表更新库存表
--下面的语句只能更新对的上的数据,更新库存数量少于出库数量
;WITH a AS (
SELECT Stock_ID,StockSkuCode,StockBatchNo,SkuSta,StockQty,StockZQty,[库存_SUM] = (
SELECT SUM(StockQty)
FROM WMS_Stock
WHERE StockSkuCode = a.StockSkuCode AND
StockBatchNo = a.StockBatchNo AND
SkuSta = a.SkuSta AND
Stock_ID <= a.Stock_ID
)
FROM dbo.WMS_Stock a where StockQty>0
),
b AS (
SELECT Order_ID,OrderNO,OrderListSkuCode,SkuSta,OrderListBatchNo,SUM(OrderListQty) AS OrderListQty, [出库_SUM] = (
SELECT SUM(OrderListQty)
FROM WMS_Out_OrderList
WHERE OrderListSkuCode = b.OrderListSkuCode AND
OrderListBatchNo = b.OrderListBatchNo AND
SkuSta = b.SkuSta AND
Order_ID=b.Order_ID
)
FROM dbo.WMS_Out_OrderList b
GROUP BY b.OrderNO,b.OrderListSkuCode,b.SkuSta,b.OrderListBatchNo,Order_ID
)
UPDATE a
SET StockZQty = CASE WHEN b.[出库_SUM] < a.[库存_SUM] THEN b.[出库_SUM]
ELSE a.[库存_SUM]
END - CASE WHEN b.[出库_SUM] - b.OrderListQty < a.[库存_SUM] - a.StockQty THEN a.[库存_SUM] - a.StockQty
ELSE b.[出库_SUM] - b.OrderListQty
END
FROM a
JOIN b
ON
a.StockSkuCode = b.OrderListSkuCode AND
a.StockBatchNo = b.OrderListBatchNo AND
a.SkuSta = b.SkuSta AND
b.[出库_SUM] - b.OrderListQty < a.[库存_SUM] AND
a.[库存_SUM] - a.StockQty < b.[出库_SUM]
--库存表(WMS_Stock)结果
/*Stock_ID StockDate StockSkuCode StockSkuName StockBatchNo SkuSta StockQty StockZQty StockLoca
12 2015-09-16 10:01:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20140911 C001 7 7 A1101
13 2015-09-16 10:01:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20140709 C001 13 13 A1102
14 2015-09-16 11:51:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20141219 C001 4 0 A1103
15 2015-09-16 11:51:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20131125 C001 37 5 A1104*/
--想的结果StockZQty 为10才对
--出库数量30件,只能完全匹配上25件,剩余的5件要取批次号(StockBatchNO)最小的,
那Stock_ID 15那行的StockZQty应该是10才对,请各位大牛帮忙修改一下上面的SQL语句,谢谢!
...全文
请发表友善的回复…
发表回复
Tiger_Zhao 2015-11-13
- 打赏
- 举报
-- 重置发货占用数量
UPDATE WMS_Stock
SET StockZQty = 0
-- 先按相同批次号发货
;WITH s AS (
SELECT Stock_ID,
StockSkuCode,
StockBatchNo,
StockQty,
ROW_NUMBER() OVER(PARTITION BY StockSkuCode, StockBatchNo
ORDER BY StockDate) rn
FROM WMS_Stock
)
,o AS (
SELECT OrderListSkuCode,
OrderListBatchNo,
SUM(OrderListQty) OrderListQty
FROM WMS_Out_OrderList
GROUP BY OrderListSkuCode, OrderListBatchNo
)
,r AS (
-- 第一条
SELECT s.Stock_ID,
s.StockSkuCode,
s.StockBatchNo,
CASE WHEN s.StockQty <= o.OrderListQty THEN
s.StockQty
ELSE
o.OrderListQty
END AS StockZQty,
CASE WHEN s.StockQty <= o.OrderListQty THEN
o.OrderListQty - s.StockQty
ELSE
0
END AS OrderListQty, -- 剩余出库数量
s.rn
FROM s, o
WHERE s.StockSkuCode = o.OrderListSkuCode
AND s.StockBatchNo = o.OrderListBatchNo
AND s.rn = 1
UNION ALL -- 出库数量有剩余，继续下一个库存
SELECT s.Stock_ID,
s.StockSkuCode,
s.StockBatchNo,
CASE WHEN s.StockQty <= r.OrderListQty THEN
s.StockQty
ELSE
r.OrderListQty
END AS StockZQty,
CASE WHEN s.StockQty <= r.OrderListQty THEN
r.OrderListQty - s.StockQty
ELSE
0
END AS OrderListQty,
s.rn
FROM r
JOIN s
ON s.StockSkuCode = r.StockSkuCode
AND s.StockBatchNo = r.StockSkuCode
AND s.rn = r.rn + 1
WHERE r.OrderListQty > 0
)
--SELECT * FROM r
UPDATE WMS_Stock
SET stock.StockZQty = r.StockZQty
FROM WMS_Stock stock,
r
WHERE stock.Stock_ID = r.Stock_ID
SELECT * FROM WMS_Stock
-- 再按相同产品不同批次号发货
;WITH s1 AS ( -- 已分配的库存
SELECT StockSkuCode,
SUM(StockZQty) StockZQty
FROM WMS_Stock
GROUP BY StockSkuCode
)
,s2 AS ( -- 有剩余的库存
SELECT Stock_ID,
StockSkuCode,
StockBatchNo,
StockQty - StockZQty AS StockQty,
ROW_NUMBER() OVER(PARTITION BY StockSkuCode
ORDER BY StockBatchNo, StockDate) rn
FROM WMS_Stock
WHERE StockQty > StockZQty
)
,o1 AS ( -- 出库需求
SELECT OrderListSkuCode,
SUM(OrderListQty) OrderListQty
FROM WMS_Out_OrderList
GROUP BY OrderListSkuCode
)
,o2 AS ( -- 缺少的出库需求
SELECT o1.OrderListSkuCode,
o1.OrderListQty - s1.StockZQty AS OrderListQty
FROM o1
JOIN s1
ON o1.OrderListSkuCode = s1.StockSkuCode
WHERE o1.OrderListQty > s1.StockZQty
)
,r AS (
-- 第一条
SELECT s2.Stock_ID,
s2.StockSkuCode,
CASE WHEN s2.StockQty <= o2.OrderListQty THEN
s2.StockQty
ELSE
o2.OrderListQty
END AS StockZQty,
CASE WHEN s2.StockQty <= o2.OrderListQty THEN
o2.OrderListQty - s2.StockQty
ELSE
0
END AS OrderListQty,
s2.rn
FROM s2, o2
WHERE s2.StockSkuCode = o2.OrderListSkuCode
AND s2.rn = 1
UNION ALL -- 出库数量有剩余，继续下一个库存
SELECT s2.Stock_ID,
s2.StockSkuCode,
CASE WHEN s2.StockQty <= r.OrderListQty THEN
s2.StockQty
ELSE
r.OrderListQty
END AS StockZQty,
CASE WHEN s2.StockQty <= r.OrderListQty THEN
r.OrderListQty - s2.StockQty
ELSE
0
END AS OrderListQty,
s2.rn
FROM r
JOIN s2
ON s2.StockSkuCode = r.StockSkuCode
AND s2.rn = r.rn + 1
WHERE r.OrderListQty > 0
)
--SELECT * FROM r
UPDATE WMS_Stock
SET stock.StockZQty = stock.StockZQty+r.StockZQty
FROM WMS_Stock stock,
r
WHERE stock.Stock_ID = r.Stock_ID
SELECT * FROM WMS_Stock第一次分配
Stock_ID StockDate StockSkuCode StockSkuName StockBatchNo SkuSta StockQty StockZQty StockLoca
----------- ----------------------- ------------- --------------------------- ------------ ------ -------- --------- ---------
12 2015-09-16 10:01:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20140911 C001 7 7 A1101
13 2015-09-16 10:01:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20140709 C001 13 13 A1102
14 2015-09-16 11:51:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20141219 C001 4 0 A1103
15 2015-09-16 11:51:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20131125 C001 37 5 A1104
第二次分配
Stock_ID StockDate StockSkuCode StockSkuName StockBatchNo SkuSta StockQty StockZQty StockLoca
----------- ----------------------- ------------- --------------------------- ------------ ------ -------- --------- ---------
12 2015-09-16 10:01:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20140911 C001 7 7 A1101
13 2015-09-16 10:01:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20140709 C001 13 13 A1102
14 2015-09-16 11:51:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20141219 C001 4 0 A1103
15 2015-09-16 11:51:00.000 1001001 榛果威化巧克力T3（6*16*T3） 20131125 C001 37 10 A1104
水向东流时光怎么偷 2015-11-12
- 打赏
- 举报
大哥,现在不是库存不足的问题,先按照出库表的批次号去库存匹配,剩下不够的在按批次号先进先出..
tcmakebest 2015-11-12
- 打赏
- 举报
与其花很多精力打造一条万能的UPDATE语句,不如按照每一条出库记录写UPDATE语句,用上事务若库存不足回滚就是了.
水向东流时光怎么偷 2015-11-12
- 打赏
- 举报
是先按条件分配库存,不够了,在按先进先出分配
Yole 2015-11-12
- 打赏
- 举报
Yole 2015-11-12
- 打赏
- 举报
是要做先进先出业务么？
