我有一個產(chǎn)品表，其中包含產(chǎn)品詳細信息，如名稱、價格、img 等，以及一個記錄訂單的訂單表。在訂單表上，我存儲了產(chǎn)品中唯一的 product_id，我想對結(jié)果進行分組。例如在我的產(chǎn)品表中，我有product_id, product_name, product_price1 apple 102 juice 53 pineapple 7.5例如，對于我的訂單表，我有order_id, product_id, quantity, date1 1 3 20.02.20202 3 2 22.12.20193 3 4 12.12.20204 1 2 12.02.2020我想按產(chǎn)品名稱和數(shù)量對我的 ORDERS 表進行分組例如product_name, quantity, priceapple 5 50pineapple 6 45這是我的查詢SELECT products.product_id, products.product_name,products.product_price AS price,orders.quantity AS col,orders.by_date,(SELECT SUM(price * col)) AS totalFROM productsINNER JOIN orders ON products.product_id = orders.product_idGROUP BY products.product_id, products.product_name, orders.order_id結(jié)果也不例外我不確定我做錯了什么。我需要做一個子查詢嗎？
3 回答
湖上湖
TA貢獻2003條經(jīng)驗 獲得超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
以上應該足以產(chǎn)生結(jié)果
roduct_name, quantity, price
apple 5 50
pineapple 6 45
嚕嚕噠
TA貢獻1784條經(jīng)驗 獲得超7個贊
首先在表中聚合，orders然后加入產(chǎn)品：
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
請參閱演示。
結(jié)果：
| product_id | product_name | quantity | price |
| ---------- | ------------ | -------- | ----- |
| 1 | apple | 5 | 50 |
| 3 | pineapple | 6 | 45 |
HUH函數(shù)
TA貢獻1836條經(jīng)驗 獲得超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 關注
- 190 瀏覽
添加回答
舉報
0/150
提交
取消