前端之家

  1. 首页
  2. 问答

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

• 问答

这些是我的桌子

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   |
+--------+-----------------+--------+
Hearbirds 回答：将相同数据库中不同表中的数量和数量相乘

您可以使用这样的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

Demo

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

,

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

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
mysqlsqlsql-update
本文链接：https://www.f2er.com/3130145.html

大家都在问