oracle进行按照周统计或者月统计的时候，需要按天进行分组。

但是如果某一天没有数据的话，就会缺少某一天的数据。

基本思路都是用两个表关联，一个日期表，一个业务表，日期表展示日期，与业务表日期关联。

建表插入数据有些繁琐，这里我们可以虚拟一个。

实现方式：

SELECT  TO_CHAR(TO_DATE( '2022-01-01', 'YYYY-MM-DD') + ROWNUM - 1, 'YYYY-MM-DD') DAY_TIME FROM DUALCONNECT BY ROWNUM <= TO_DATE( '2022-02-01', 'YYYY-MM-DD') - TO_DATE( '2022-01-01', 'YYYY-MM-DD')

这样就得到一张日期表

查询结果如下：

单独根据日期对业务表进行查询如下：

select TO_CHAR(to_date(t.ORDER_DATE),'yyyy-mm-dd') as DAY_TIME,sum(t.DEAL_AMOUNT) as totalMoney from  BASE_ORDER t where t.dr = 0 and t.ORDER_DATE>= to_date('2022-01-01','YYYY-MM-DD')and t.ORDER_DATE<= to_date('2022-02-01','YYYY-MM-DD') GROUP BY to_date(t.ORDER_DATE) 

两张表进行关联

SELECT	to_date( a.day_time, 'YYYY-MM-DD' ),	nvl( b.totalMoney, 0 ) FROM	(	SELECT		TO_CHAR( TO_DATE( '2021-10-01', 'YYYY-MM-DD' ) + ROWNUM - 1, 'YYYY-MM-DD' ) DAY_TIME 	FROM		DUAL CONNECT BY ROWNUM <= TO_DATE( '2021-10-31', 'YYYY-MM-DD' ) - TO_DATE( '2021-10-01', 'YYYY-MM-DD' ) 	) a	LEFT JOIN (	SELECT		TO_CHAR( to_date( t.ORDER_DATE ), 'yyyy-mm-dd' ) AS DAY_TIME,		sum( t.TOTAL_DEAL_AMOUNT ) AS totalMoney 	FROM		BASE_ORDER t 	WHERE		t.dr = 0 		AND t.ORDER_DATE >= to_date( '2021-10-01', 'YYYY-MM-DD' ) 		AND t.ORDER_DATE <= to_date( '2021-10-31', 'YYYY-MM-DD' ) 	GROUP BY		to_date( t.ORDER_DATE ) 	) b ON a.day_time = b.day_time ORDER BY	a.day_time ASC

查询结果

如果按年分12个月，则先找到月份表

SELECT CONCAT('2021-',CONCAT(case when ROWNUM<10 then '0' else '' end, ROWNUM))  DAY_TIME FROM DUAL CONNECT BY ROWNUM <= 12

同样进行左关联

SELECT	to_date( a.day_time, 'yyyy-mm' ),	nvl( b.totalMoney, 0 ) FROM	(	SELECT	CONCAT( '2021-', CONCAT( CASE WHEN ROWNUM < 10 THEN '0' ELSE '' END, ROWNUM ) ) DAY_TIME FROM	DUAL CONNECT BY ROWNUM <= 12 	) a	LEFT JOIN (	SELECT		TO_CHAR( to_date( t.ORDER_DATE ), 'yyyy-mm' ) AS DAY_TIME,		sum( t.TOTAL_DEAL_AMOUNT ) AS totalMoney 	FROM		BASE_ORDER t 	WHERE		t.dr = 0 		AND t.ORDER_DATE >= to_date( '2021-01-01', 'yyyy-mm-dd' ) 		AND t.ORDER_DATE <= to_date( '2022-01-01', 'yyyy-mm-dd' ) 	GROUP BY		TO_CHAR( to_date( t.ORDER_DATE ), 'yyyy-mm' ) 	) b ON a.day_time = b.day_time ORDER BY	a.day_time ASC

查询结果