如何在没有游标的情况下实现多对多匹配

Question

>我有两个表订单和库存。

create table #orders
(orderId int,
demand decimal,
itemId int)
create table #inventory 
(invId int,
qtyOnHand decimal,
itemId int)
insert into #orders 
values(101, 2, 999)
insert into #orders 
values(102, 1, 999)
insert into #inventory
values(601, 1, 999)
insert into #inventory
values(602, 4, 999)
insert into #inventory
values(603, 1, 999)

我需要将相应的库存数量分配给排序订单。所以预期的结果是：

orderID - inventoryID - reservedQty
101       601          1
101       602          1
102       602          1

主要问题是 #inventory.qtyOnHand在我将其分配给订单时正在更改

我已经尝试过汇总所有库存

 SELECT
itemId,
sum(qtyOnHand) as qtyOnHand,
STUFF((
SELECT ', ' + cast(invId as varchar)
FROM #inventory
WHERE (itemId = inv.itemId)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS inv_info
into #inv_aggr
FROM #inventory inv
GROUP BY itemId
select #orders.orderId, 
case  when ( #inv_aggr.qtyOnHand >= (select sum(o.demand) from #orders o where  o.itemId = #orders.itemId and o.orderId <= #orders.orderId))
then #orders.demand
else #inv_aggr.qtyOnHand - (select coalesce(sum(demand), 0) from #orders o where o.itemId = #orders.itemId and o.orderId < #orders.orderId)
end as qty_rsvd,
#inv_aggr.inv_info
from #orders
inner join #inv_aggr
on #inv_aggr.itemId =#orders.itemId and #inv_aggr.qtyOnHand > 0

但这无济于事，因为我可以按库存拆分和匹配订单

我将不胜感激任何关于如何实现这一点或如何将这项任务分成几个的建议。

Answer 1

喜欢库存/订单管理问题，尝试完全基于集合(没有循环(，但没有像大多数人那样递归。递归增加了复杂性、潜在的性能挑战以及遇到无限循环的风险

/*Raw data setup*/
DROP TABLE IF EXISTS #Orders
DROP TABLE IF EXISTS #Inventory
DROP TABLE IF EXISTS #OrderRange
DROP TABLE IF EXISTS #InventoryRange
CREATE TABLE #Orders(OrderID INT,Demand INT,ItemID INT)
CREATE TABLE #Inventory (InvID INT,QtyOnHand INT,ItemID INT)
INSERT INTO #Orders 
VALUES(101, 2,999)
,(102, 1,999)
,(103,10,222)
,(104,1,222)
,(105,4,222)
INSERT INTO #Inventory
VALUES(601, 1,999)
,(602, 4,999)
,(603, 1,222)
,(604,2,222)
,(605,2,222)
/*Setup work tables
Essentially ranges number each inventory and reserves a range of ID's for each order*/
SELECT *
    ,LowerRange = SUM(Demand) OVER (PARTITION BY ItemID ORDER BY OrderID) - Demand + 1
    ,UpperRange = SUM(Demand) OVER (PARTITION BY ItemID ORDER BY OrderID)
INTO #OrderRange
FROM #Orders
SELECT *
    ,LowerRange = SUM(QtyOnHand) OVER (PARTITION BY ItemID ORDER BY InvID)- QtyOnHand + 1
    ,UpperRange = SUM(QtyOnHand) OVER (PARTITION BY ItemID ORDER BY InvID) 
INTO #InventoryRange
FROM #Inventory
/*Final query*/
SELECT 
     O.OrderID
    ,I.InvID
    ,O.ItemID
    ,C.ReservedQty
    ,O.Demand
    ,I.QtyOnHand
    ,QtyFulfilled = SUM(C.ReservedQty) OVER (PARTITION BY O.OrderID,O.ItemID ORDER BY I.InvID) /*When this less than Demand OR NULL, then order not enough stock to fulfill order*/
    ,QtyRemaining = O.Demand - SUM(ISNULL(C.ReservedQty,0)) OVER (PARTITION BY O.OrderID,O.ItemID ORDER BY I.InvID) /*Inverse basically of QtyFulfilled*/
FROM #OrderRange AS O
LEFT JOIN #InventoryRange AS I
    ON O.ItemID = I.ItemID
    AND (
        /*Overlap between order range and inventory ranges*/
        O.LowerRange BETWEEN I.LowerRange and I.UpperRange
        OR O.UpperRange BETWEEN I.LowerRange and I.UpperRange
        /*For when entire inventory range between order range*/
        OR I.UpperRange BETWEEN O.LowerRange AND O.UpperRange
        )
CROSS APPLY (
    /*Finds the count of overlap between order range and inventory range
    Essentially orders all range values and finds the two middle numbers, then calculates the delta of them
    Ex. If you have a range of 2 to 5 and 3 to 6
        It sorts the numbers as 2,3,5,6
        It grabs the second value 3 (OFFSET 1 FETCH 1), then because LEAD runs before OFFSET it grabs the next row so 5
        The delta of 3 and 5 = 2 so there's a 2 number overlap between the two ranges
    */
    SELECT ReservedQty = LEAD(DTA.Points,1) OVER (ORDER BY DTA.Points) - DTA.Points + 1
    FROM (VALUES(O.LowerRange),(O.UpperRange),(I.LowerRange),(I.UpperRange)) AS DTA(Points)
    ORDER BY Points
    OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
            ) AS C
ORDER BY O.Orderid,I.InvID