php - 当月销售总额

Question

我有以下sql:

SELECT CONCAT('', DAY(OrderUpdated), '') AS OrderDate, SUM(OrderTotal) AS ChartOrderTotal
FROM Orders WHERE OrderUpdated >= '2018-1-1' AND OrderUpdated <= '2018-2-1' 
GROUP BY CONCAT(LEFT(MONTHNAME(OrderUpdated), 3), ' ', DAY(OrderUpdated), ',', YEAR(OrderUpdated)), DAYOFWEEK(OrderUpdated) 
ORDER BY DAY(OrderUpdated) ASC

以上 SQL 显示结果为:

2, 20000
5, 2000
7, 8200
15, 1300
20, 1200

现在，如果我希望所有缺失的天数也为零。如:

1, 0
2, 20000
3, 0
4, 0
....
20,1200
21, 0
22, 0
....

还有我正在使用手动输入的日期。如何在 WHERE 子句中选择当前月份。

仅供引用:订单更新日期为 CURRENT_TIMESTAMP

Answer 1

您还应该按 ID 进行分组，以便可以获得所有值:

SELECT CONCAT('', DAY(OrderUpdated), '') AS OrderDate, SUM(OrderTotal) AS ChartOrderTotal
FROM Orders WHERE OrderUpdated >= '2018-1-1' AND OrderUpdated <= '2018-2-1' 
GROUP BY CONCAT(LEFT(MONTHNAME(OrderUpdated), 3), ' ', DAY(OrderUpdated), ',', YEAR(OrderUpdated)), DAYOFWEEK(OrderUpdated), ID
ORDER BY DAY(OrderUpdated) ASC