oracle空日期,Oracle自动补全日期，空数据为0

1.问题描述html

在数据库查询中计算年月周日的数据时候，会发现日期并不完整，但愿日期能够自动补全，而且没有数据的字段补0sql

最开始的数据展示为以下图数据库

select nvl(sum(synnum),0) value ,to_char(syntime, 'yyyy-MM-dd') lable from synlog where syntype in (4, 5,6) and trunc(syntime) > trunc(sysdate) - 30 group by to_char(syntime, 'yyyy-MM-dd') order by 2;   最近30天数据查询出来只有2条数据，这并非我想要获得的

2.具体实现spa

思路为：须要有一个最近30天的日期表，而后进行一个左关联，值为空即补0code

最近一月日期表htm

select calendar_day

from (select to_date((select to_char(sysdate , 'yyyy') from dual) || '0101', 'yyyymmdd') + level-1 as calendar_day from dual

connect by level

where calendar_day>= trunc(sysdate) - 29 and calendar_day

3.最终实现blog

select a.calendar_day,

nvl(b.value,0) as value from

(select calendar_day

from (select to_date((select to_char(sysdate+1 , 'yyyy') from dual) || '0101', 'yyyymmdd') + level - 2 as calendar_day from dual

connect by level <=to_char(to_date((select to_char(sysdate+1, 'yyyy') from dual) || '1231', 'yyyymmdd'), 'ddd'))

where calendar_day>= trunc(sysdate) - 29 and calendar_day

left join

(select nvl(sum(synnum),0) value ,

to_date( to_char(syntime, 'yyyymmdd'), 'yyyymmdd') lable from

synlog where syntype in (4, 5, 6) and trunc(syntime) > trunc(sysdate) - 29 group by to_date( to_char(syntime, 'yyyymmdd'), 'yyyymmdd') )b

on a.calendar_day = b.lable order by a.calendar_day

效果图：class