MySQL:可以根据每个收到订单的金额计算总库存成本吗？

Question

我想通过减去收到的订单来计算总库存成本，直到收到的订单数量与库存水平相符。这样我就能获得准确的总库存成本。

我有下表用于各个订单收据行。 order_receipt_article

id   order_receipt_id   article_id   quantity   price
1    1000               456          10         100
2    1001               456          5          120
3    1002               456          15         140
4    1003               456          20         100
5    1004               456          5          90

还有一张库存表

id   article_id   level
1    456          43

现在我想要手头库存的总值(value)，可以这样计算

(5 x 90) + (20 x 100) + (15 x 140) + (3 x 120) = 4910

我会发布我已经尝试过的内容，但我不知道从哪里开始解决这个问题。

/编辑

我不是要求基本的 SUM() 我总共收到了 55 个单位，但有 43 个库存。我想根据收到的单位了解库存的确切值(value)。因此，查看第一个表，最旧的订单 (id 1) 将不会用于计算，因为在订单 2 t/m 5 内，我总共收到了 45 个订单。

Answer 1

这应该可以解决您的问题，它使用 session 变量来保持处理的单元数的运行总数。不同寻常的是，如果没有临时表，我就无法让它工作，因为排序无法正常工作，这是我发现解决此问题的唯一方法。

DROP TABLE IF EXISTS order_receipt_article_test;
CREATE TABLE order_receipt_article_test(id INT UNSIGNED, order_receipt_id INT UNSIGNED, article_id INT UNSIGNED, quantity INT UNSIGNED, price INT UNSIGNED);

DROP TABLE IF EXISTS inventory_test;
CREATE TABLE inventory_test(id INT UNSIGNED, article_id INT UNSIGNED, `level` INT UNSIGNED);

INSERT INTO order_receipt_article_test VALUES
    (1, 1000, 456, 10, 100),
    (2, 1001, 456, 5, 120),
    (3, 1002, 456, 15, 140),
    (4, 1003, 456, 20, 100),
    (5, 1004, 456, 5, 90);

INSERT INTO inventory_test VALUES
    (1, 456, 43);

SET @VArticleId := 0;
SET @VCumulativeQuantity := 0;

DROP TEMPORARY TABLE IF EXISTS ZSort;

CREATE TEMPORARY TABLE ZSort
SELECT A.article_id, A.`level`, B.quantity, B.price, B.order_receipt_id FROM 
    inventory_test A
LEFT OUTER JOIN
    order_receipt_article_test B
ON A.article_id = B.article_id
ORDER BY A.article_id, B.order_receipt_id DESC;

SELECT article_id, SUM(rowPrice) FROM
    (
    SELECT @VCumulativeQuantity := IF(article_id != @VArticleId, `level`, @VCumulativeQuantity) levelReset, IF(@VCumulativeQuantity < quantity, @VCumulativeQuantity * price, quantity * price) rowPrice, @VCumulativeQuantity := IF(@VCumulativeQuantity < quantity, 0, @VCumulativeQuantity - quantity) quantityAdjust, @VArticleId := article_id article_id FROM ZSort
    ) A
GROUP BY article_id;

问候，

詹姆斯