将数量和数量相乘在同一数据库的不同表中

debugcn 发表于 Dev

摇篮

这些是我的桌子

ORDER_ITEMS

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

PRODUCT_INGREDIENT：

+--------+-----------------+--------+
|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表中的（金额）列乘以ORDER_ITEMS表中的列（数量）

这是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

我希望查询查询后的库存如下所示：

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

scaisEdge

查找数据样本似乎需要添加子查询的结果

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。