在同一数据库中不同表中计算数量和金额的乘积

Question

这是我的表格

订单条目 (ORDER_ITEMS)

+----------+--------+-----------------+------------+
| order_id |item_id |    item_name    |  quantity  |
+----------+--------+-----------------+------------+
|     1    |   1    |      coffee     |     2      |
|     2    |   2    |      shake      |     2      |
|     2    |   3    |    icecream     |     3      |
+----------+--------+-----------------+------------+

产品成分：

+--------+-----------------+--------+
|item_id | ingredient_id   | amount |
+--------+-----------------+--------+
|   1    |      123        |  10    |
|   1    |      124        |  15    |
|   2    |      125        |  10    |
|   2    |      124        |  15    |
|   2    |      123        |  10    |
|   2    |      126        |  15    |
|   3    |      124        |  15    |
|   3    |      123        |  10    |
|   3    |      126        |  15    |
+--------+-----------------+--------+

库存：

+--------+-----------------+--------+
|  id    | ingredient_id   | amount |
+--------+-----------------+--------+
|   1    |      123        |   80   |
|   2    |      124        |   70   |
|   3    |      125        |  100   |
|   4    |      126        |  100   |
+--------+-----------------+--------+

我已经有一个工作的SQL语句，但我需要将PRODUCT_INGREDIENT表中的(amount)列乘以ORDER_ITEMS表中的(quantity)列。

以下是SQL语句：

UPDATE inventory i 
INNER  JOIN (
  SELECT p.ingredient_id, sum(p.amount) amount
    FROM product_ingredient p 
   INNER JOIN  order_items o on o.item_id = p.item_id
   WHERE o.order_id = 1
   GROUP BY p.ingredient_id
) p ON  i.ingredient_id = p.ingredient_id 
SET i.amount = i.amount - p.amount 

我希望在查询后我的库存看起来像这样：

INVENTORY:

+--------+-----------------+--------+
|  id    | ingredient_id   | amount |
+--------+-----------------+--------+
|   1    |      123        |  100   |
|   2    |      124        |  100   |
|   3    |      125        |  100   |
|   4    |      126        |  100   |
+--------+-----------------+--------+

- reve berces

Answer 1

您可以使用以下 UPDATE 语句：

UPDATE inventory i 
 INNER JOIN (
  SELECT p.ingredient_id, sum(p.amount*o.quantity) amount
  FROM product_ingredient p 
  INNER JOIN  order_items o on o.item_id = p.item_id
  WHERE o.order_id = 1
  GROUP BY p.ingredient_id
) p ON  i.ingredient_id = p.ingredient_id 
SET i.amount = i.amount + p.amount 

演示

仅有的 ingredient_id 123 和 124 能够匹配整个查询。因此，只有它们被更新。

- Barbaros Özhan

---

它显示一个错误：“在字段列表中未知列'o.quantity'”。 - reve berces

但是您的表order_items中有一个名为quantity的列，不是吗？另外，请查看之前已经分享的演示。 - Barbaros Özhan

是的，抱歉我在我的数据库中拼错了它。我已经修复了它，现在它完美地工作了，除了你添加了（SET i.amount = i.amount + p.amount）而不是减去。非常好的工作，谢谢，但我已经标记了另一个答案为正确答案，虽然我给你点了赞。 - reve berces

Answer 2

根据您提供的数据样本，似乎需要添加子查询的结果。

UPDATE inventory i 
INNER  JOIN (
  select p.ingredient_id, sum(p.amount*o.quantity) amount
  from product_ingredient p 
  INNER JOIN  order_items o on o.item_id = p.item_id
  WHERE o.order_id = 1
  GROUP BY p.ingredient_id
) p ON  i.ingredient_id = p.ingredient_id 
SET i.amount = i.amount + p.amount 

- ScaisEdge

---

它显示一个错误：“在字段列表中未知列'o.quantity'”。 - reve berces

在您的表模式/数据示例中，您在订单项表中有一个名为“quantity”的列。您确定名称确实是“quantity”，而不是“qty”或其他名称吗？最终，请向我们展示订单项表的真实表模式。 - ScaisEdge

我在数据库中拼错了“数量”，我已经更正了，现在它看起来是这样的：库存，123=120，124=130，而不是123=80，124=70。 - reve berces

我只是把加法改成了减法UPDATE inventory i INNER JOIN ( select p.ingredient_id, sum(p.amount*o.quantity) amount from product_ingredient p INNER JOIN order_items o on o.item_id = p.item_id WHERE o.order_id = 1 GROUP BY p.ingredient_id ) p ON i.ingredient_id = p.ingredient_id SET i.amount = i.amount - p.amount - reve berces

@Remaster，你只能标记一个答案为已接受，但由于你的声望超过15，你也可以将所有你认为有用的答案标记为有用（点击每个答案左上角的箭头）。所以，如果你愿意，你也可以给其他人的答案点赞（包括我的）。 - ScaisEdge