玖叶教程网

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

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

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

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

实现方式：

SELECT  
TO_CHAR(TO_DATE( '2022-01-01', 'YYYY-MM-DD') + ROWNUM - 1, 'YYYY-MM-DD') DAY_TIME 
FROM DUAL
CONNECT 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

查询结果