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
效果图: