mysql - 按月分组，然后返回月份，按年份过滤结果为0

This question already has an answer here:

MySql Count cannot show 0 values
                                
                                    （1个答案）
                                
                        
                                2年前关闭。
            
                    
我试图按月对给定表的结果进行分组，并返回没有值的地方，我创建了另一个表，但是我无法按年份过滤条目（例如，where YEAR(created) = 2018），但是内部查询没有返回预期的月份。

我有这个查询。

SELECT
    months.name as m,
    count(MONTH(created_at)) as total
from entries
RIGHT JOIN months
    ON MONTH(created_at) = months.id
where YEAR(created_at) = 2018
GROUP BY months.id

查询的结果是这样的：

+----+------------------+---------------------------+
| id | month            | total                     |
+----+------------------+---------------------------+
|  1 | january          | 27                        |
|  3 | march            | 3                         |
|  5 | may              | 2                         |
+----+------------------+---------------------------+

我想实现以下目标：

+----+------------------+-------+
| id | month            | total |
+----+------------------+-------+
|  1 | january          | 27    |
|  2 | february         | 0     |
|  3 | march            | 3     |
|  4 | april            | 0     |
|  5 | may              | 2     |
|  6 | june             | 0     |
|  7 | july             | 0     |
|  8 | august           | 0     |
|  9 | september        | 0     |
| 10 | october          | 0     |
| 11 | november         | 0     |
| 12 | december         | 0     |
+----+------------------+-------+

我假设我的where year...子句是导致这种行为的那个子句，如何使我的查询像我想要实现的那样工作？

最佳答案

尝试执行左联接而不是右联接

SELECT months.name as m,
   IFNULL(Count(created_at), 0) as total
  from months
  LEFT JOIN ENTRIES ON months.id = MONTH(created_at)
  where YEAR(created_at) = 2018
  GROUP BY months.id