将缺少的月份添加到查询结果中 [英] Add missing month into query result

问题描述

我有一个非常简单的查询: http://pastebin.com/CGPZM14U

I have a very simple query: http://pastebin.com/CGPZM14U

它统计给定年份每月的文章.假设我在2014年8月没有写任何东西.显然，而不是8月= 0，我将在7月之后是9月.

It counts articles per month in a given year. Let's assume that I have nothing written in August 2014. Obviouslty instead of having August = 0 I will have September next after July.

如何添加计数等于0的缺失月份.我有一些可行的示例，但我正在寻找尽可能简单，干净的解决方案.

How to add missing months with count equal to 0. I have some working examples but I'm looking for as simple and clean solution as possible.

推荐答案

创建一个包含所有月份的表，然后将其与您的查询联接.遵循

Create a table with all the months, and then left join it with your query. Along the lines of

select count(1) as count, months.month, year(published_at) as year
from months left join articles on months.month = month(published_at)
where published_at >= (now() - interval 1 year) and published_at <= now()
group by month(published_at)
order by year asc, month asc

虽然可以，但是它将返回您从1月到12月的结果，并且不会给您几年的时间.之前曾问过类似的问题，然后我提出了一个非常复杂的查询，因此，如果您没有发现更简单的方法，请考虑一下:

It will kind of work, but it will return you results from Jan to Dec, and won't give you years. A similar question was asked before, then I came up with a very complex query, so if you don't find anything simpler, consider taking a look:

日期之间的查询无法正常工作，现在我们进入了新的一年

这篇关于将缺少的月份添加到查询结果中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！