mysql - 通过 MySQL 中的查询提供缺失值

Question

我的 MySQL 数据库中有一个表可供我查询。我正在查询特定月份的日期和销售数量。我的查询如下所示:

SELECT DATE_FORMAT(time,'%b-%y') AS "date", COUNT(*) AS "value"
FROM table
WHERE time_in BETWEEN concat('2014-01-01',' 00:00:00') AND concat('2014-11-25',' 23:59:59')
GROUP BY month(time);

注意:我知道这种情况下的连接是完全不必要的，我将它们替换为“？”在我的 PHP 后端中进行数据绑定(bind)。

此查询的结果类似于 JSON:

[
{"date":"Feb-14","value":"2"},
{"date":"Mar-14","value":"1"},
{"date":"May-14","value":"1"},
{"date":"Jun-14","value":"1"},
{"date":"Jul-14","value":"2"},
{"date":"Sep-14","value":"1"},
{"date":"Oct-14","value":"1"},
{"date":"Nov-14","value":"1"}
]

如您所见，我错过了几个月(一月、四月等)。我的问题是，有没有办法可以通过查询提供这些缺失的月份并填充 0 值？我希望我的输出如下所示:

[
{"date":"Jan-14","value":"0"},
{"date":"Feb-14","value":"2"},
{"date":"Mar-14","value":"1"},
{"date":"Apr-14","value":"0"},
{"date":"May-14","value":"1"},
{"date":"Jun-14","value":"1"},
{"date":"Jul-14","value":"2"},
{"date":"Aug-14","value":"0"},
{"date":"Sep-14","value":"1"},
{"date":"Oct-14","value":"1"},
{"date":"Nov-14","value":"1"}
]

Answer 1

要扩展我的评论，您可以执行以下操作:-

SELECT  DATE_FORMAT(DATE_SUB(NOW(), INTERVAL month_cnt.iCnt MONTH), '%b-%y') AS MonthYear, COUNT(id)
FROM
(SELECT 0 iCnt 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) month_cnt
LEFT OUTER JOIN table
ON MONTH(DATE_SUB(NOW(), INTERVAL month_cnt.iCnt MONTH)) = MONTH(table.time_in)
AND YEAR(DATE_SUB(NOW(), INTERVAL month_cnt.iCnt MONTH)) = YEAR(table.time_in)
GROUP BY MonthYear

它有一个返回数字 0 到 11 的子查询。然后，它根据数据月份与当前日期的月份相同减去返回的数字，将该子查询与您的数据表连接起来。子查询。然后它计算该月/年表中存在的记录