SQL查询表中返回nil的日期并不present [英] SQL query to return nil for dates not present in the table

问题描述

我有一个表my_table的。它具有以下数据：

I have a table 'my_table'. It has the following data :

ID  ---   Date         

1   ---   01/30/2012  
2   ---   01/30/2012   
3   ---   05/30/2012  

我可以写一个SQL查询返回的ID的特定日期之间，按月份进行分组数，像这样：

I can write a SQL query to return the count of ID's between certain dates, grouped by month, like this :

{"01/30/2012" => 2, "05/30/2012" => 1}

我怎样才能得到一个结果是有要求的日期之间的所有缺失个月的值为0，像这样的：

How can I get a result which has all the missing months between the requested dates with value '0', like this :

{"01/30/2012" => 2, "02/30/2012" => 0, "03/30/2012" => 0, "04/30/2012" => 0, "05/30/2012" => 1} 

在此先感谢。

Thanks in advance.

推荐答案

我做到这一点是有一个静态表的所有日期的列表的方式。你的情况这是30日各月（怎么样二月？）。让我们把这个表REF_DATE。它有一个单一的柱DT保存日期

The way I do it is to have a static table with list of all the dates. In your case that's 30th of each month (what about February?). Lets call this table REF_DATE. It has a single column DT that holds the date.

假设my_table的只包含0或最多1不同的日期（30日）在每个月，你需要做的是：

Assuming that my_table only contains 0 or at most 1 distinct date (30th) in each month, what you need to do is:

select DT,count(ID) from 
REF_DT REF 
left outer join my_table MT 
on REF.DT=my_table.DATE
group by REF.DT;

这篇关于SQL查询表中返回nil的日期并不present的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！