如何编写SQL查询统计指定客户下单的各商品总购买数量

正确SQL写法 #

SELECT 
  CUS.customer_id AS `CUSTOMER ID`,
  ORD.product_id AS `PRODUCT_ID`,
  SUM(ORD.quantity) AS `总购买量SUM(QUANTITIES)`
FROM `customers` CUS
INNER JOIN `orders` ORD
  ON ORD.customer_id = CUS.customer_id
WHERE CUS.customer_id = 4697
GROUP BY CUS.customer_id, ORD.product_id

逻辑说明 #

• 原写法没有聚合规则，会返回所有订单明细行，增加GROUP BY按「客户ID+商品ID」分组，即可合并同款商品的所有订单记录
• 这里把LEFT JOIN替换为INNER JOIN，因为统计的是已产生订购记录的商品，无订单的客户空记录不需要保留
• 如果需要额外带出客户名称、商品名称等字段，可以把PRODUCTS表加回关联，同时把需要展示的非聚合字段加到SELECT和GROUP BY列表中即可，拓展写法参考：

SELECT 
  CUS.customer_id AS `CUSTOMER ID`,
  CUS.customer_name,
  ORD.product_id AS `PRODUCT_ID`,
  PRO.name AS product_name,
  SUM(ORD.quantity) AS `总购买量SUM(QUANTITIES)`
FROM `customers` CUS
INNER JOIN `orders` ORD
  ON ORD.customer_id = CUS.customer_id
INNER JOIN `products` PRO
  ON PRO.product_id = ORD.product_id
WHERE CUS.customer_id = 4697
GROUP BY CUS.customer_id, CUS.customer_name, ORD.product_id, PRO.name

内容的提问来源于stack exchange，提问作者Beko