oracle 按天數統計資料____oracle

當使用group by crateTime時,但是某天沒有資料時,就出現某天沒有資料了.

所以使用以下sql,能把每天的都統計出來.

        select 

            to_date('2015-06-01', 'yyyy-mm-dd')+level -1 as 時間         from dual  connect by to_date('2015-06-01', 'yyyy-mm-dd') + level -1 <= to_date('2015-06-04', 'yyyy-mm-dd');              select             (to_date('2015-06-01','yyyy-mm-dd')+level - 1) as 時間         from dual connect by level <= to_date('2015-06-04','yyyy-mm-dd') - to_date('2015-06-01','yyyy-mm-dd') + 1;

               查詢結果如下:

時間
2015-6-1
2015-6-2
2015-6-3
2015-6-4

然後再使用left on 連結去查詢統計資料:如

select c_time,nvl(totalUser,0) as total_user from (        select   (to_date('2015-06-01','yyyy-mm-dd')+level - 1) as c_time  from dual connect by level <= to_date('2015-06-11','yyyy-mm-dd') - to_date('2015-06-01','yyyy-mm-dd') + 1        ) a left join (          select trunc(u.create_time) as create_time,count(*) as totalUser from t_user u group by trunc(u.create_time)        ) b on a.c_time = b.create_time order by c_time