我每月都有一套帖子.现在我需要一个数组,其中包含每个月发布的帖子的总记录.我在
MySql下面尝试查询,它的工作正常,但是在没有记录的情况下,我期待0(零)几个月.这里没有返回0.
我读到COUNT()不会返回’0′,那我怎么做到这一点?
我尝试了IFNULL()和COALESCE()但仍然得到了相同的结果.请帮助解决此问题.谢谢……
SELECT
count(id) as totalRec
FROM ('post')
WHERE year(date) = '2013'
AND monthname(date) IN ('January', 'February', 'March')
GROUP BY year(date)-month(date)
ORDER BY 'date' ASC
得到的结果:
+----------+
| totalRec |
+----------+
| 7 |
| 9 |
+----------+
预期结果(1月份没有帖子):
+----------+
| totalRec |
+----------+
| 0 |
| 7 |
| 9 |
+----------+
样本数据:
+----+---------------------+
| id | date |
+----+---------------------+
| 24 | 2012-12-16 16:29:56 |
| 1 | 2013-02-25 14:57:09 |
| 2 | 2013-02-25 14:59:37 |
| 4 | 2013-02-25 15:12:44 |
| 5 | 2013-02-25 15:14:18 |
| 7 | 2013-02-26 11:31:31 |
| 8 | 2013-02-26 11:31:59 |
| 10 | 2013-02-26 11:34:47 |
| 14 | 2013-03-04 04:39:02 |
| 15 | 2013-03-04 05:44:44 |
| 16 | 2013-03-04 05:48:29 |
| 19 | 2013-03-07 15:22:34 |
| 20 | 2013-03-15 12:24:43 |
| 21 | 2013-03-16 16:27:43 |
| 22 | 2013-03-16 16:29:28 |
| 23 | 2013-03-16 16:29:56 |
| 11 | 2013-03-17 11:35:12 |
+----+---------------------+