java - 如何获取一段时间内特定日期的出现情况

Question

我有两个日期，例如 2-Mar-16 和 22-Aug-16，我想确定日期 25 在此期间出现了多少次。我如何创建这样的查询？

Answer 1

下面的查询将为您提供日期范围内 25 的出现次数。我内联编写的逻辑。

select substr(DAT,1,2) numbr,count (substr(DAT,1,2))  --- Filtering only day part of date and counting it
from 
(
--Generating all the dates between 2 dates
select to_date('02-Mar-2016','dd-mm-yyyy') + LEVEL  DAT    
from dual 
connect by level < (select ABS (to_date('02-Mar-2016','dd-mm-yyyy') - to_date('22-Aug-2016','dd-mm-yyyy') )
                    from dual)
)
where substr(DAT,1,2) = 25
group by substr(DAT,1,2);