Mysql查询，按月获取各商品的当前销售总额

Question

这是我的sql代码，我想要显示每个产品的当月总销售额，在这个图像中我们可以看到10月份的总结果pro_id =175，我想显示每个产品每个月的条目，很多产品有循环吗？我将使用php在html表格中显示结果。

SELECT  MONTHNAME(`order_date`), SUM(`current-sales`) AS TotalCS,  `nsp`, `pro_id`
FROM `orders` 
WHERE YEAR(order_date) = 2017 AND MONTH(order_date) = 10
  AND pro_id = 175

Result by October

Answer 1

使用GROUP BY。这样，您将获得2017年前每个pro_id的当前销售量总和

SELECT  MONTHNAME(order_date), SUM(current-sales) AS TotalCS, pro_id
FROM orders
WHERE YEAR(order_date) = 2017
GROUP BY MONTH(order_date), pro_id, nsp

Answer 2

Try datepart函数这应该有效：

SELECT datepart('month','order_date'), SUM(`current-sales`) AS TotalCS, `nsp`, `pro_id` 
FROM `orders` 
WHERE datepart('year','order_date') = '2017' AND datepart('month','order_date')= '10' AND pro_id = 175

十月份的结果

SELECT datepart('month','order_date'), SUM(`current-sales`) AS TotalCS, `nsp`, `pro_id` 
FROM `orders` 
WHERE datepart('year','order_date') = '2017' AND datepart('month','order_date')= '10' AND
Group by datepart('month','order_date');

适用于所有月份