将缺少的月份添加到查询结果中 [英] 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屋!