Oracle查询某个时间段内第天的统计数

1：Oracle查询某个时间段内，每天发送短信的总条数

select tab.tday,
       (select count(1) from t_msgsendrecode m where trunc(m.sendtime) = to_date(tab.tday,‘yyyy-mm-dd‘))
from (

select * from (
select to_char(add_months(last_day(to_date(‘2015-6-2‘,‘yyyy-mm-dd‘))+1,-1),‘YYYY-MM-DD‘) as tday from dual
union all
select to_char(add_months(last_day(to_date(‘2015-6-2‘,‘yyyy-mm-dd‘))+1,-1) + rownum,‘YYYY-MM-DD‘) as tday
from dual
connect by rownum <= (last_day(to_date(‘2015-6-2‘,‘yyyy-mm-dd‘)) - add_months(last_day(to_date(‘2015-6-2‘,‘yyyy-mm-dd‘)) +1, -1))
)

) tab

2：查询两个日期相隔天数据

select rownum dis from (
select max(to_date(‘2015-6-30‘,‘yyyy-mm-dd‘)-to_date(‘2015-6-1‘,‘yyyy-mm-dd‘))+1 as gap from dual
)  connect by rownum <= gap

Oracle查询某个时间段内第天的统计数

标签：