不使用 SQL 游标插入行和更新表

Question

我正在尝试创建一个 MS SQL 2005 存储过程来为订单中的特定组件分配仓库中的库存。有多个批次的可用库存，并且必须按特定顺序使用。我可以循环遍历可用库存并进行分配，直到订单完成，但我试图更多地考虑基于集合而不是顺序，并避免使用游标。

这是我的查询，用于获取特定订单组件可用的仓库库存；

SELECT 
        STOCK.ComponentId,
        STOCK.StockId,
        STOCK.ExpiryDate, 
        STOCK.BatchNo, 
        STOCK.StockQty, 
        ORDER_ITEMS.OrderQty        
    FROM 
        STOCK 
        JOIN ORDER_ITEMS ON ORDER_ITEMS.ComponentId = STOCK.ComponentId 
    WHERE 
        STOCK.WarehouseId = @WarehouseId 
        AND STOCK.StockQty > 0
        AND ORDER_ITEMS.OrderItemId = @OrderItemId

我已将其放入临时表中或使用查询创建 CTE 并应用 ORDER BY 子句来对需要用完的库存进行排序。这给了我一个如下的结果集：

ComponentId | StockId | ExpiryDate | BatchNo  | StockQty | OrderQty
-------------------------------------------------------------------
359         | 3107    | 2013-10-01 | 132435-1 | 20       | 50
359         | 3215    | 2013-10-01 | 154558-1 | 100      | 50
359         | 3216    | 2014-01-01 | 154689-1 | 100      | 50

我需要做的是使用满足订单数量所需的尽可能多的库存批次将记录插入到 STOCK_ALLOCATED 表中。在上面的示例中，我将使用第一行中的全部 20 个，然后需要第二行中的 30 个。

这需要将两条记录插入到 STOCK_ALLOCATED 表中，其中包含两个已用批次的 OrderItemId、StockId 以及数量（20 和 30）并适当减少STOCK表中的库存数量。

假设必要的事务已到位以一致地维护库存表，是否有一种方法可以在不使用游标循环和跟踪我已经分配了多少库存以及我还需要多少库存的情况下进行插入和更新？

Answer 1

这是一个适合您的测试示例：

SELECT  
    StockID,
    Quantity
FROM (
    SELECT  
        StockID,
        CASE 
            WHEN OrderQty - PreviousQty <= 0 THEN 0
        ELSE 
                CASE 
                    WHEN OrderQty - PreviousQty <= Stock 
                    THEN OrderQty - PreviousQty 
                ELSE
                    Stock 
                END
        END Quantity
    FROM (
        SELECT  
            a.StockID,
            a.Stock,
            a.OrderQty,
            a.OrderByField,
            ISNULL(SUM(b.Stock), 0) PreviousQty
        FROM    
            @Table a
            LEFT JOIN @Table b ON a.OrderByField > b.OrderByField
        GROUP BY a.StockID,
            a.Stock,
            a.OrderQty,
            a.OrderByField
        ) Orders
    ) Orders
WHERE   Quantity > 0

基本上，您需要将自己获得的结果结合起来，以便您可以将先前保留的数量相加，然后从 StockQty 中减去该数量以确定您还需要哪些数量需要满足。

不过，为了做到这一点，您将需要一个唯一的 OrderByField，以便您可以准确地过滤掉以前的 StockQty 值。如果您没有可以使用的存储值，则可以使用 ROW_NUMBER() OVER 派生它。如果是这样，请告诉我您是否需要帮助。