php - 循环中的 SQL 更新(mysql、PHP)

Question

我有两张 table :

订单:

date | Product | Quantity

01/03| P1 | 2

01/03| P2 | 2

02/03| P1 | 3

02/03| P2 | 1

02/03| P3 | 5

库存:

Purchase number | product | Quantity

01/03 | P1 | 4

01/03 | P2 | 1

02/03 | P2 | 2

02/03 | P3 | 5

02/03 | P1 | 1

02/03 | P1 | 1

第一个表是我的订单(我售出的商品)，通过这个表我想更新第二个表(这是我的库存)以准确了解我的库存。但我想在日期之后执行此操作，我首先在 02/03 之前更新 Quantity.Stock 01/03 。现在我有一个循环，问题是我不想有任何“-1”并且我不想用相同的数据更新所有行。在此示例中，我按顺序有 5 个 P1，因此库存中 P1 的第一行必须为 0 ，下一个 P1 行也必须为 0 但最后一行必须保持为 1 。

有什么想法吗？或者方向？

布鲁诺

Answer 1

我认为 date 和 buy_number 是某种日期时间值。获取您尚未使用的订单。我想您有一些使用/处理过的列或表，您将它们保存为标记。

SELECT id, product, quantity
FROM orders
WHERE used = 0 AND quantity > 0
ORDER BY date ASC

获取您可以使用的所有相关购买内容，以便按照您想要使用的顺序(从最早的开始)。

SELECT id, product, quantity
FROM stock
WHERE quantity > 0
ORDER BY purchase_number ASC

然后，您可以反复标记订单并更新购买情况。我假设您将结果分别保存到 $orders 和 $purchases 中。

foreach ($orders as $order) {
    $remaining = $order['quantity'];

    foreach ($purchases as &$purchase)
        if ($order['product'] !== $purchase['product']
            || $purchase['quantity'] === 0) {
            continue;
        }

        $remaining = $purchase['quantity'] - $remaining;
        $purchase['quantity'] = max($remaining, 0);

        // update purchase, where
        //     :quantity is $purchase['quantity']
        //     :id is $purchase['id']
        // UPDATE stock SET quantity = :quantity WHERE id = :id

        if ($remaining >= 0) {
            break;
        }

        $remaining = -$remaining;
    }
    unset($purchase);

    if ($remaining > 0) {
        // we have problem, we sold more, then we have in stock
    }

    // mark order as used, where :id is $order['id']
    // UPDATE orders SET used = 1 WHERE id = :id
}

Example has time complexity O(M*N), where M is orders and N is purchases. You can change it to O(M+N), when you group data by products and do M SQL queries for purchases or some preprocessing. It has memory complexity O(M+N), which is a tradeoff for less queries.