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屋！