Oracle 统计日期数据，没有日期的自动补全，自动补0

1. 问题

最近在项目中遇到一个问题，在进行oracle相关查询统计的时候，查询某个月的数据，如果有几天没有数据，就造成了日期的缺失，
我想做的就是不管有没有数据所有数据都要查询出来，没有日期的自动补全，自动补0

2. 直接上最终代码

自动补齐月份

SELECT
	TO_CHAR( ADD_MONTHS( TO_DATE( '2018-01', 'yyyy-MM' ), ROWNUM - 1 ), 'yyyy-MM' ) AS monthlist 
FROM
	DUAL CONNECT BY ROWNUM <= months_between( to_date( '2018-12', 'yyyy-MM' ), to_date( '2018-01', 'yyyy-MM' ) ) + 1

3. 实践

这个表就两个月的数据，要统计一年12月的
image-1682022411896

3.1 正常统计12个月的（没有补齐的）

SELECT
   to_char( TIMES, 'yyyy-mm' ) AS TIMES,
   sum( NUMS ) AS nums 
FROM
   MONTH_DATAS 
WHERE
   TIMES BETWEEN to_date( '2023-01', 'yyyy-mm' )  AND to_date( '2023-12', 'yyyy-mm' ) 
GROUP BY
   to_char( TIMES, 'yyyy-mm' )

效果
image-1682022919202

可以看到就俩月的，下面加上补齐

3.2 补齐后

SELECT
	t1.monthItem,
	nvl( t2.nums, 0 ) AS value 
FROM (
	--12个月的 t1
      SELECT
      	TO_CHAR( ADD_MONTHS( TO_DATE( '2023-01', 'yyyy-MM' ), ROWNUM - 1 ), 'yyyy-MM' ) AS monthItem
      FROM
      	DUAL CONNECT BY ROWNUM <= months_between( to_date( '2023-12', 'yyyy-MM' ), to_date( '2023-01', 'yyyy-MM' ) ) + 1 
      ) t1
	--左联你的查询 t2
      LEFT JOIN (
      	  SELECT
      	     TO_CHAR( TIMES, 'yyyy-mm' ) AS TIMES,
      	     sum( NUMS ) AS nums 
      	  FROM
      	     MONTH_DATAS 
      	  WHERE
      	     TIMES BETWEEN to_date( '2023-01', 'yyyy-mm' )  AND to_date( '2023-12', 'yyyy-mm' ) 
      	  GROUP BY TO_CHAR( TIMES, 'yyyy-mm' ) 
      ) t2 ON t1.monthItem = t2.TIMES 
ORDER BY
	t1.monthItem

image-1682023343206

4. 附上补齐天数的

4.1 sql

SELECT
		TO_CHAR( TO_DATE( '2023-08-01', 'yyyy-MM-dd' ) + ROWNUM - 1, 'yyyy-MM-dd' ) AS daylist 
	FROM
		DUAL CONNECT BY ROWNUM <= trunc(
		to_date( '2023-08-31', 'yyyy-MM-dd' ) - to_date( '2023-08-01', 'yyyy-MM-dd' )) + 1

4.2 trunc用法

select trunc(sysdate) from dual； --2023-04-21 今天的日期为2023-04-21
select trunc(sysdate, 'mm') from dual； --2023-04--01 返回当月第一天.
select trunc(sysdate,'yy') from dual ；--2023-01--01 返回当年第一天
select trunc(sysdate,'dd') from dual ；--2023-04--21 返回当前年月日
select trunc(sysdate,'yyyy') from dual ；--2023-01--01 返回当年第一天
select trunc(sysdate,'d') from dual ；--2023-04-16 (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual； --2023-04-21 17:00:00 当前时间为17:35 
select trunc(sysdate, 'mi') from dual ；--2023-04-21 17:35:00 TRUNC()函数没有秒的精确

总结：

自动补齐月份 使用这个查到所有的月份然后左联接你的查询nvl( nums , 0 ) 就行了

SELECT
	TO_CHAR( ADD_MONTHS( TO_DATE( '2018-01', 'yyyy-MM' ), ROWNUM - 1 ), 'yyyy-MM' ) AS monthlist 
FROM
	DUAL CONNECT BY ROWNUM <= months_between( to_date( '2018-12', 'yyyy-MM' ), to_date( '2018-01', 'yyyy-MM' ) ) + 1