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

来源：互联网发布：淘宝企业店铺企业资质编辑：程序博客网时间：2024/05/17 23:52

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) aleft 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')  )bon a.calendar_day = b.lable order by a.calendar_day

效果图：

阅读全文

0 0