我需要一些关于MySQL查询的帮助。
我有两张表一张是产品清单一张是仓库数量清单
产品:
product_id product_name
1 name1
2 name2
3 name3
仓库产品
id warehouse_id product_id product_quantity
1 1 1 15
2 2 1 30
3 1 2 100
4 2 2 30
5 1 3 20
6 2 3 40
我希望从上述数据中得到的结果是
product_id product_name product_quantity
1 name1 45
2 name2 130
3 name3 60
我试过很多次,但都没用。我的问题是:
SELECT
product_id as product_id, SUM(quantity) as quantity
FROM
(SELECT
p.product_id as product_id, wp.product_quantity as quantity
FROM
product as p
LEFT JOIN warehouse_product as wp ON p.product_id = wp.product_id
WHERE
product_active = 1)
最佳答案
试试这个:
SELECT p.product_id, p.product_name, SUM(wp.quantity) as quantity
FROM product as p
LEFT JOIN warehouse_product as wp
ON p.product_id = wp.product_id
AND product_active = 1
GROUP BY p.product_id, p.product_name
在连接两个表之后,您需要使用
GROUP BY
以便可以计算每个产品的SUM
数量。关于mysql - 联接两个表。一列使用SUM,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/21013881/