mysql - 按月分组，如果找不到数据则返回 0

Question

几天来我一直在尝试修复我的查询:(。它应该按月分组，如果找不到记录则返回 0。一个例子:

    SELECT MONTH(created_at) AS month_num, 
DATE_FORMAT(created_at, '%b') AS month_name, COUNT(*) AS total_num 
    FROM table WHERE user_id=1384249399168
    GROUP BY MONTH(created_at) ORDER BY created_at DESC

结果:

month_num       month_name       total_num
     8            Aug                 20 
     7            Jul                 15
     5            May                 39
     2            Feb                 10
     1            Jan                 8

预期结果:

month_num       month_name       total_num
         12           Dec                 0
         11           Nov                 0
         10           Oct                 0
         9            Sep                 0
         8            Aug                 20 
         7            Jul                 15
         6            Jun                 0
         5            May                 39
         4            Apr                 0
         3            Mar                 0
         2            Feb                 10
         1            Jan                 8

我已经尝试了很多答案，尤其是这个 MySql count() to return 0 if no records found但没有成功。

有什么帮助吗？

Answer 1

我还没有测试过，但它应该可以工作:

SELECT MONTH(created_at) AS month_num, 
DATE_FORMAT(created_at, '%b') AS month_name, ifnull(count(*),0) AS total_num 
    FROM table WHERE user_id=1384249399168
    GROUP BY MONTH(created_at) ORDER BY created_at DESC