这是我的表格
订单条目 (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 |
+--------+-----------------+--------+
order_items
中有一个名为quantity
的列,不是吗?另外,请查看之前已经分享的演示。 - Barbaros Özhan