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

发布于 2024-11-15 22:16:03 字数 1364 浏览 0 评论 0原文

我正在尝试创建一个 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 表中,其中包含两个已用批次的 OrderItemIdStockId 以及数量(20 和 30)并适当减少STOCK表中的库存数量。

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

I'm trying to create a MS SQL 2005 stored procedure to allocate stock from a warehouse for a particular component in an order. There are multiple batches of stock available and these have to be used in a particular order. I can loop through the available stock and allocate until the order is fulfilled but I'm trying to think more set based than sequential and avoid using a CURSOR.

Here is my query that gets the warehouse stock available for a particular order component;

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

I've been putting this into a temp table or creating a CTE with the query and applying an ORDER BY clause to get the stock sorted as it needs to be used up. This gives me a result set like:

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

What I need to do is insert records into a STOCK_ALLOCATED table using as many of the batches of stock as necessary to fulfil the Order quantity. In the example above I would use all 20 from the first row and then need 30 from the second row.

This would need to insert two records into a STOCK_ALLOCATED table with the OrderItemId, StockId and the quantity (20 and 30) for the two used batches and also decrease the stock quantity in the STOCK table appropriately.

Assuming that the necessary transactions are in place to maintain the stock tables consistently is there a way to do the inserts and updates without using a CURSOR to loop through and track how much stock I've already allocated and how much I still require?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

假情假意假温柔 2024-11-22 22:16:03

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

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 派生它。如果是这样,请告诉我您是否需要帮助。

This is a test sample that will work for you:

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

Basically, you will need to join the results you are getting on itself, so that you can sum the quantities of the previous reserved quantities, and subtract this from the StockQty to determine what you still need to fulfill.

In order to do that, though, you will need a unique OrderByField so that you can accurately filter out the previous StockQty values. If you don't have a stored value you can use, you can derive it using ROW_NUMBER() OVER. If that's the case, let me know if you need help with that.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文