匹配条件问题大牛们帮忙看看

[水向东流时光怎么偷]

--出库表
CREATE TABLE WmsOutboundOrderLines ( [id] nvarchar(50), [orderNumber] nvarchar(50), [erpNumber] nvarchar(50), [itemCode] nvarchar(50), [packageType] nvarchar(50), [quantity] bigint, [itemStatus] nvarchar(50), [batchCode] nvarchar(50), [remarks] nvarchar(500), [createBy] nvarchar(50), [createTime] datetime, [updateBy] nvarchar(50), [updateTime] datetime )
INSERT INTO WmsOutboundOrderLines
VALUES
( N'FC16163C-397A-470E-AEF7-93D230BAA9A1', N'OGR1126001', N'EGR1126001', N'itemCode1', N'itemCodeName', 10, N'ZP', N'', N'', N'Test', N'2019-11-26T09:45:41.1', NULL, NULL ), 
( N'F1AC3E71-8971-4064-B35E-E3ACB91F1966', N'OGR1126001', N'EGR1126001', N'itemCode2', N'itemCodeName2', 10, N'ZP', N'B001', N'', N'Test', N'2019-11-26T09:46:00.08', NULL, NULL )

DROP TABLE WmsOutboundOrderLines
--库存表
CREATE TABLE WmsStorage ( [id] nvarchar(50), [orderNumber] nvarchar(50), [erpNumber] nvarchar(50), [itemCode] nvarchar(50), [packageType] nvarchar(50), [quantity] bigint, [zquantity] bigint, [itemStatus] nvarchar(50), [batchCode] nvarchar(50), [remarks] nvarchar(500), [createBy] nvarchar(50), [createTime] datetime, [updateBy] nvarchar(50), [updateTime] datetime )
INSERT INTO WmsStorage
VALUES
( N'7EE9C5F5-DC6A-40D1-8B08-750E9E58E92F', N'IGR1126001', N'EGR1126001', N'itemCode1', N'itemCodeName', 20, 0, N'ZP', N'B001', N'', N'Test', N'2019-11-26T09:46:41.967', NULL, NULL ), 
( N'59B2F30D-7FF0-4D9C-8A6A-C73DCEBD99AA', N'IGR1126001', N'EGR1126001', N'itemCode2', N'itemCodeName2', 20, 0, N'ZP', N'B001', N'', N'Test', N'2019-11-26T09:47:00.277', NULL, NULL ), 
( N'59B2F30D-7FF0-4D9C-8A6A-C73DCEBDA8A', N'IGR1126001', N'EGR1126001', N'itemCode2', N'itemCodeName2', 20, 0, N'ZP', N'', N'', N'Test', N'2019-11-26T09:47:00.277', NULL, NULL )

DROP TABLE WmsStorage

--如果出库表batchCode为空在匹配库存的时候就不用batchCode字段为判断条件,如果batchCode不为空则要给库存进匹配
--Bacth这个条件应该怎么写?,根据出库数量占用库存表的zquantity字段
--点用sql过程
;WITH a
AS (SELECT id,
           itemCode,
           itemStatus,
           batchCode,
           quantity,
           zquantity,
           [库存_SUM] =
           (
               SELECT SUM(quantity)
               FROM WmsStorage
               WHERE itemCode = a.itemCode
                     AND itemStatus = a.itemStatus
                     AND batchCode = a.batchCode
                     AND id <= a.id
           )
    FROM dbo.WmsStorage a
    WHERE quantity > 0),
      b
AS (SELECT id,
           itemCode,
           itemStatus,
           batchCode,
           SUM(quantity) AS OrderListQty,
           [出库_SUM] =
           (
               SELECT SUM(quantity)
               FROM WmsOutboundOrderLines
               WHERE itemCode = b.itemCode
                     AND itemStatus = b.itemStatus
                     AND batchCode = b.batchCode
                     AND id = b.id
           )
    FROM dbo.WmsOutboundOrderLines b
    WHERE b.orderNumber = 'OGR1126001'
    GROUP BY id,
             itemCode,
             itemStatus,
             batchCode)
UPDATE a
SET zquantity = CASE
                    WHEN b.[出库_SUM] < a.[库存_SUM] THEN
                        b.[出库_SUM]
                    ELSE
                        a.[库存_SUM]
                END - CASE
                          WHEN b.[出库_SUM] - b.OrderListQty < a.[库存_SUM] - a.quantity THEN
                              a.[库存_SUM] - a.quantity
                          ELSE
                              b.[出库_SUM] - b.OrderListQty
                      END
FROM a
    JOIN b
        ON a.itemCode = b.itemCode
           AND a.itemStatus = b.itemStatus
           AND a.batchCode = b.batchCode
           AND b.[出库_SUM] - b.OrderListQty < a.[库存_SUM]
           AND a.[库存_SUM] - a.quantity < b.[出库_SUM];
--执行后结果库存表

SELECT *FROM dbo.WmsStorage

/*
--占用结果不正确,这样写的话出库订单批次号为空,但库存批次号不为空,没有匹配上
id	orderNumber	erpNumber	itemCode	packageType	quantity	zquantity	itemStatus	batchCode	remarks	createBy	createTime	updateBy	updateTime
7EE9C5F5-DC6A-40D1-8B08-750E9E58E92F	IGR1126001	EGR1126001	itemCode1	itemCodeName	20	0	ZP	B001		Test	2019-11-26 09:46:41.967	NULL	NULL
59B2F30D-7FF0-4D9C-8A6A-C73DCEBD99AA	IGR1126001	EGR1126001	itemCode2	itemCodeName2	20	10	ZP	B001		Test	2019-11-26 09:47:00.277	NULL	NULL
59B2F30D-7FF0-4D9C-8A6A-C73DCEBDA8A	IGR1126001	EGR1126001	itemCode2	itemCodeName2	20	0	ZP			Test	2019-11-26 09:47:00.277	NULL	NULL
*/

--正确结果
/*
id	orderNumber	erpNumber	itemCode	packageType	quantity	zquantity	itemStatus	batchCode	remarks	createBy	createTime	updateBy	updateTime
7EE9C5F5-DC6A-40D1-8B08-750E9E58E92F	IGR1126001	EGR1126001	itemCode1	itemCodeName	20	10	ZP	B001		Test	2019-11-26 09:46:41.967	NULL	NULL
59B2F30D-7FF0-4D9C-8A6A-C73DCEBD99AA	IGR1126001	EGR1126001	itemCode2	itemCodeName2	20	10	ZP	B001		Test	2019-11-26 09:47:00.277	NULL	NULL
59B2F30D-7FF0-4D9C-8A6A-C73DCEBDA8A	IGR1126001	EGR1126001	itemCode2	itemCodeName2	20	0	ZP			Test	2019-11-26 09:47:00.277	NULL	NULL
*/

[水向东流时光怎么偷]

如果出库订单有批次号要必须按批次号与库存表匹配