sql返回没有结果的日期 [英] sql return dates where there are no results
问题描述
我想得到一个显示X包含条目的日期的结果表
I want to get a table of results showing the dates that X has entries
SELECT count(*),
date_column
FROM myTable
WHERE X
GROUP BY date_column
ORDER BY date_column DESC
这可行,但是我也想看看X没有条目的日期,在我的用例中,这是中间日期。
This works, but I would also like to see the dates where X does not have entries, in my use case this would be intermediary dates.
例如,结果中将包含2013-3-10,但是下一个日期将是2013-3-5,但是我需要我的结果还返回count = 0的日期,因此在这种情况下,第6,第7,第8和第9
So for instance 2013-3-10 would be in the results, but the next date would be 2013-3-5, yet I need my result to also return the days where count = 0, so in this case, the 6th, 7th, 8th and 9th
如何格式化查询以包括额外的时间?
how would I format my query to include those extra times?
推荐答案
我模拟了一个简单的示例:
I mocked up a simple example:
SELECT q.date_column, count(f.id) FROM
(SELECT
generate_series(min(date_column),max(date_column), '1 day') AS date_column
FROM mytable) AS q
LEFT JOIN mytable AS f
ON q.date_column=f.date_column
GROUP BY q.date_column ORDER BY q.date_column;
这会生成所需范围内的所有日期。确保不要做count(*),否则您将得到1而不是0
http ://sqlfiddle.com/#!1 / fd4ff / 1
This generates all dates in the needed range. make sure not to do count(*) or you'll get 1 instead of 0 http://sqlfiddle.com/#!1/fd4ff/1
这篇关于sql返回没有结果的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!