动态创建日期范围并针对您的订单表进行合并：-
SELECT sub1.sdate, COUNT(ORDERS.id) as Norders
FROM
(
SELECT DATE_FORMAT(DATE_SUB(Now(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY), "%M %e") as sdate
FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)hundreds
WHERE DATE_SUB(Now(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) BETWEEN DATE_SUB(Now(), INTERVAL 1 MONTH) AND Now()
) sub1
LEFT OUTER JOIN ORDERS
ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%M %e")
GROUP BY sub1.sdate
这可以处理多达1000天的日期范围。
请注意，根据您用于日期的字段类型，可以轻松提高效率。
编辑-根据要求，以获取每月的订单数：-
SELECT aMonth, COUNT(ORDERS.id) as Norders
FROM
(
SELECT DATE_FORMAT(DATE_SUB(Now(), INTERVAL months.i MONTH), "%Y%m") as sdate, DATE_FORMAT(DATE_SUB(Now(), INTERVAL months.i MONTH), "%M") as aMonth
FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11)months
WHERE DATE_SUB(Now(), INTERVAL months.i MONTH) BETWEEN DATE_SUB(Now(), INTERVAL 12 MONTH) AND Now()
) sub1
LEFT OUTER JOIN ORDERS
ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%Y%m")
GROUP BY aMonth
602392714
清零编程群