SQL通过临时表查询过去近几年的历史数据

经常会碰到查询过去几年的数据统计、类似历史数据之类的报表，往往需要统计过去多少多少年的数据，直接根据时间字段分组查，如果没有数据的话就会查不到数据，所以一般先生成时间序列临时表然后再left join一下，对应时间没有数据可以了。
在此做个记录~

select (2022 - rownum + 1) AS year from dual connect by 10 > rownum

往前一周

select to_char (sysdate- level + 1, 'yyyy-mm-dd') today FROM DUAL connect BY LEVEL <= 7

通过函数生成具体一点的时间

ALTER FUNCTION [tm_DateList](@stm varchar(30),@etm varchar(30),@format int,@lpt int)
RETURNS  @x table([date] datetime PRIMARY KEY)
AS
begin 
    -- declare  @x table([date] datetime PRIMARY KEY);
	with temptab(date) as
	( select CONVERT(datetime,@stm,@format)
	union all
	select dateadd(hh,1*@lpt,temptab.date) as date
	from temptab
	where dateadd(hh,1*@lpt,temptab.date)<=CONVERT(datetime,@etm,@format)
	)
	Insert into @x select date from temptab	OPTION (MAXRECURSION 0)
   return 
end

又例如查询过去一年的月平均雨量，需要先生成一年的时间序列

一年

select to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+rownum as time,rownum as d from all_objects where rownum < 365 ORDER BY time

通过组合拼接出来一年的旬，如果为了方便排序业通常可以将上中下置为类似ABC字符

 select 
 DISTINCT(to_char(time,'mm') ||'-' ||(case when to_char(time,'dd') <='10' then '上旬'
 when to_char(time,'dd') <='20' then '中旬'
 else '下旬' END) )AS TT		
 from 
 (select to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+rownum as time,rownum  as d from all_objects where rownum < 365 ORDER BY time) ORDER BY TT