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查詢某個時間段內第天的統計數