❤️Oracle数据库中查询一个时间段内的记录，如果其中某一天的记录没有，则取最近一天的记录填充

小明的学习圈子2023-12-07数据库Oracle

实现1

SELECT
	T2.time AS countDate,
	count( T2.id ) AS count 
FROM
	(
	SELECT
		temp.time,
		T1.CREATE_DATE,
		T1.ID 
	FROM
		( SELECT TO_CHAR( TO_DATE( '2023-12-07', 'yyyy-MM-dd' ) + ROWNUM - 7, 'yyyy-MM-dd' ) AS time FROM DUAL CONNECT BY ROWNUM < 8 ) temp
		LEFT JOIN TGA_VEHICLE_RECORDS T1 ON TO_CHAR( T1.CREATE_DATE, 'YYYY-MM-DD' ) = temp.time 
	ORDER BY
		temp.time 
	) T2 
GROUP BY
	T2.time 
ORDER BY
	T2.time

查询数据库一周的统计数据

SELECT COUNT(ID) FROM TGA_VEHICLE_RECORDS T1 where  TO_CHAR( T1.CREATE_DATE, 'YYYY-MM-DD' ) BETWEEN  TO_CHAR( TO_DATE( '2023-12-01', 'yyyy-MM-dd' ), 'yyyy-MM-dd' )  and TO_CHAR( TO_DATE( '2023-12-07', 'yyyy-MM-dd' ), 'yyyy-MM-dd' )

完善版字符串

SELECT
	T2.count_date AS countDate,
	COUNT( T2.id ) AS count 
FROM
	(
	SELECT
		TEMP.count_date,
		T1.CREATE_DATE,
		T1.CHANNEL_TYPE,
		T1.VEHICLE_TYPE,
		T1.ID 
	FROM
		( SELECT TO_CHAR( TO_DATE( '2023-12-07', 'yyyy-MM-dd' ) + ROWNUM - 7, 'yyyy-MM-dd' ) AS count_date FROM DUAL CONNECT BY ROWNUM < 8 ) TEMP
		LEFT JOIN TGA_VEHICLE_RECORDS T1 ON TO_CHAR( T1.CREATE_DATE, 'YYYY-MM-DD' ) = TEMP.count_date AND  T1.CHANNEL_TYPE =1 
		AND T1.VEHICLE_TYPE = '1' 
	ORDER BY
		TEMP.count_date 
	) T2 
GROUP BY
	T2.count_date 
ORDER BY
	T2.count_date

完善版当前日期

SELECT
	T2.count_date AS countDate,
	COUNT( T2.id ) AS count 
FROM
	(
	SELECT
		TEMP.count_date,
		T1.CREATE_DATE,
		T1.CHANNEL_TYPE,
		T1.VEHICLE_TYPE,
		T1.ID 
	FROM
		( SELECT TO_CHAR( SYSDATE + ROWNUM - 7, 'yyyy-MM-dd' ) AS count_date FROM DUAL CONNECT BY ROWNUM < 8 ) TEMP
		LEFT JOIN TGA_VEHICLE_RECORDS T1 ON TO_CHAR( T1.CREATE_DATE, 'YYYY-MM-DD' ) = TEMP.count_date 
		AND T1.CHANNEL_TYPE = 1 
		AND T1.VEHICLE_TYPE = '1' 
	ORDER BY
		TEMP.count_date 
	) T2 
GROUP BY
	T2.count_date 
ORDER BY
	T2.count_date