Mysql 触发器将不同表中的两个值相乘，按组相加并将值添加到另一列

Question

我已经解决了一段时间，但我无法弄清楚。假设我有这三个表：

Users
Name   | Basket value
-------+---------------      
John   |     ???
-------+--------------- 
Pierre |     ???

Items
User       |     Item   |     Amount
-----------+------------+------------
John     |    Pears   |       2
-----------+------------+------------
Pierre   |    Pears   |       1
-----------+------------+------------
Pierre   |   Apples   |       3

Market_ prices
Item        |        Price
------------+---------------
Pears       |        2.35
------------+---------------
Apples      |      2.56

需要购物篮值。因此，对于 ITEMS 中的每一行，它必须将其AMOUNT乘以MARKET_PRICES[PRICE]并将按USER分组的所有结果相加，并将此结果放在USERS[total items value]中。但是，如何详细阐述语法以将其付诸实践呢？

提前非常感谢您的帮助。

Answer 1

一种方法是连接相关表：

SELECT u.name, SUM(i.amount*m.price)
FROM users u 
JOIN items i ON u.name = i.user
JOIN market_prices m ON i.item = m.item
GROUP BY u.name;

(SQL小提琴在： http://sqlfiddle.com/#!9/ec224/6 - 我在表格中添加了其他几行以测试更多复杂性，因此总数不是您从示例中得到的。具体来说，我向Market_prices表添加了Bananas 3.75，并将John Apples 3和Pierre Bananas 5添加到"项目"表中。

这里的目标是通过共享字段(通过 JOIN(链接所有三个表中的信息，同时对计算成本进行分组并创建总和作为市场价格和项目数量的乘积。

----根据评论进行编辑---- 要将其作为更新，您可以尝试：

UPDATE users 
SET basket_value = (SELECT basket FROM
(SELECT name, SUM(i.amount*m.price) AS basket
FROM users u JOIN items i ON u.name = i.username 
JOIN market_prices m ON i.item = m.item
GROUP BY u.name) q1 WHERE q1.name = users.name);

我有一种感觉，有一个更优雅的解决方案，但这有效。修改后的 SQLFiddle 为：http://sqlfiddle.com/#!9/56245a/1

Answer 2

SELECT User, SUM(rev) AS basket_price
FROM
(
SELECT a.User AS User, a.Amount*b.Price AS rev 
FROM Items a
LEFT JOIN
Market_Prices b 
ON a.Item = b.Item
) a1
GROUP BY User