3 回答
![?](http://img1.sycdn.imooc.com/5458463b0001358f02200220-100-100.jpg)
TA贡献2003条经验 获得超2个赞
SELECT
p.product_name,
SUM(o.quantity) AS quantity,
SUM(o.quantity * p.product_price) AS price
FROM products p
INNER JOIN orders o
ON p.product_id = o.product_id
GROUP BY
p.product_name
以上应该足以产生结果
roduct_name, quantity, price
apple 5 50
pineapple 6 45
![?](http://img1.sycdn.imooc.com/5458655200013d9802200220-100-100.jpg)
TA贡献1784条经验 获得超7个赞
首先在表中聚合,orders然后加入产品:
SELECT
p.product_id,
p.product_name,
o.quantity,
o.quantity * p.product_price AS price
FROM products p INNER JOIN(
SELECT product_id, SUM(quantity) AS quantity
FROM orders
GROUP BY product_id
) o ON o.product_id = p.product_id
请参阅演示。
结果:
| product_id | product_name | quantity | price |
| ---------- | ------------ | -------- | ----- |
| 1 | apple | 5 | 50 |
| 3 | pineapple | 6 | 45 |
![?](http://img1.sycdn.imooc.com/5458626a0001503602200220-100-100.jpg)
TA贡献1836条经验 获得超4个赞
不需要子查询...
SELECT p.product_id, p.product_name
, SUM(o.quantity)
, SUM(o.quantity) * p.product_price AS price
FROM products AS p
INNER JOIN orders AS o
ON p.product_id = o.product_id
GROUP BY p.product_id, p.product_name, p.price
;
- 3 回答
- 0 关注
- 154 浏览
添加回答
举报