oracle数据自动补日期,oracle补齐日期

生成日期列表

SELECT to_date( as first_login_day,

ROWNUM -

FROM DUAL

CONNECT BY ROWNUM <=

trunc(sysdate - ) -

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

as first_login_day,

ROWNUM -

FROM DUAL

CONNECT BY ROWNUM <=

trunc(sysdate - ) -

to_date() t13,

(SELECT to_date( as fill_day,

ROWNUM -

FROM DUAL

CONNECT BY ROWNUM <=

trunc(sysdate - ) -

to_date() 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