oracle补齐日期

生成日期列表

SELECT to_date('2017-3-14', 'yyyy-mm-dd') + ROWNUM - 1 as first_login_day,
                               ROWNUM - 1
                          FROM DUAL
                        CONNECT BY ROWNUM <=
                                   trunc(sysdate - 1) -
                                   to_date('2017-3-14', 'yyyy-mm-dd'

 

范例：生成日期和补全某天充值数据

select t11.fill_day,
               t11.first_login_day,               
               t12.day_fill_total
          from 
          
          (select t13.first_login_day, t14.fill_day
                  from (SELECT to_date('2017-3-14', 'yyyy-mm-dd') + ROWNUM - 1 as first_login_day,
                               ROWNUM - 1
                          FROM DUAL
                        CONNECT BY ROWNUM <=
                                   trunc(sysdate - 1) -
                                   to_date('2017-3-14', 'yyyy-mm-dd') + 1) t13,
                       (SELECT to_date('2017-3-14', 'yyyy-mm-dd') + ROWNUM - 1 as fill_day,
                               ROWNUM - 1
                          FROM DUAL
                        CONNECT BY ROWNUM <=
                                   trunc(sysdate - 1) -
                                   to_date('2017-3-14', 'yyyy-mm-dd') + 1) t14
                 where t14.fill_day >= t13.first_login_day) t11
                 left join
          (select t5.first_login_day,
                       t6.fill_day,
                       sum(t6.day_fill_total) day_fill_total
                -- t5.first_login_day, t5.channel, t5.accountid, t6.fill_day,  t6.day_fill_total 
                  from t5
                 right outer join (
                                  -- 每日充值
                                  select trunc(t6.stat_time) as fill_day,
                                          t6.accountid,
                                          sum(t6.paymoney) as day_fill_total
                                    from Fill_TABLE t6
                                   where STAT_TIME >= date'2017-3-14'                                     
                                   group by trunc(t6.stat_time), t6.accountid) t6
                    on t5.accountid = t6.accountid
                 group by t5.first_login_day, t6.fill_day) t12
                  on t11.first_login_day = t12.first_login_day and t11.fill_day = t12.fill_day