SQL - 通过添加和不同的产品来选择客户的订单

Question

我有3个表:

客户:

CUSTOMER_ID | CUSTOMER_NAME

订单:

ORDER_ID | CUSTOMER_ID | PRODUCT_ID | QUANTITY | PRICE

产品:

PRODUCT_ID | NAME | PRICE

我想列出客户订购的所有产品，但要加上所有数量。

这是我想要的结果:

CUSTOMER ID | PRODUCT_ID(1) | QUANTITIES (SUM)
CUSTOMER ID | PRODUCT_ID(2) | QUANTITIES (SUM)
CUSTOMER ID | PRODUCT_ID(3) | QUANTITIES (SUM)
CUSTOMER ID | PRODUCT_ID(4) | QUANTITIES (SUM)

这是我的实际请求，仅列出所有订单的所有信息(没有SUM):

SELECT CUS.*, ORD.*, PRO.*
FROM `customers` CUS
LEFT JOIN `orders` ORD
ON ORD.customer_id = CUS.customer_id
LEFT JOIN `products` PRO
ON PRO.product_id = ORD.product_id 
WHERE CUS.customer_id = 4697

谢谢

Answer 1

您需要使用sum和group by，只选择您想要的列也会使它更容易。

select customer_id, product_id, sum(quantity)
FROM `customers` CUS
LEFT JOIN `orders` ORD
ON ORD.customer_id = CUS.customer_id
LEFT JOIN `products` PRO
ON PRO.product_id = ORD.product_id 
WHERE CUS.customer_id = 4697
group by customer_id, product_id

Answer 2

SELECT
PRO.product_name,
ROUND(SUM(ORD.price),2),
ROUND(SUM(ORD.quantity), 2)
FROM ORDERS ORD
LEFT JOIN PRODUCTS PRO
ON PRO.product_id = ORD.product_id
WHERE ORD.customer_id = 4697
GROUP BY ORD.product_id

谢谢;)