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

1.问题描述

   在数据库查询中计算年月周日的数据时候，会发现日期并不完整，希望日期可以自动补全，并且没有数据的字段补0

   最开始的数据展现为如下图

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.具体实现  

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

     最近一月日期表     

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 <to_char(to_date((select to_char(sysdate, 'yyyy') from dual) || '1231', 'yyyymmdd'), 'ddd'))
where calendar_day>= trunc(sysdate) - 29 and calendar_day<trunc(sysdate)+1

3.最终实现

   

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<trunc(sysdate)+1) a
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

   效果图：