按月分组并在未找到数据时返回 0 [英] Group by month and return 0 if data not found

问题描述

我已经尝试修复我的查询好几天了:(.它应该按月分组，如果没有找到记录，则返回 0.一个例子:

I have been trying to fix my query for days now :(. It is supposed to group by month and return 0 if record is not found. An example:

    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 计数() 如果没有找到记录，则返回 0 但没有成功.

I have tried a lot of answers especially this MySql count() to return 0 if no records found but without success.

请问有什么帮助吗?

推荐答案

这篇文章解决了我的问题.我必须创建一个包含所有 12 个月的表格.

This post solved my problem. I had to create a table which contains all the 12 months.

mysql选择每个月的记录数感谢任何人，尤其是@Turdaliev

mysql select the count of records for every month Thanks anyone especially @Turdaliev

这篇关于按月分组并在未找到数据时返回 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！