Sql Server常用命令整理篇：生成连续日期序列并循环

1、生成日期序列
特别注意：经验证number不能超过2048，超过部分不可用

--生成日期序列，特别注意，经验证number不能超过2048，超过部分不可用
select observedate sdate,DATEADD(hour,23,observedate) edate from (
SELECT
    DATEADD(DAY,number,CAST(@i as varchar)+'-01-01 00:00:00') observedate
FROM
    master..spt_values 
WHERE
    type = 'p'
AND number<=DATEDIFF(day,CAST(@i as varchar)+'-01-01 00:00:00',CAST(@i as varchar)+'-12-31 00:00:00')
) x

2、循环插入或者这更新，循环语句写法
特别注意：在sql server中数字类型和字符串拼接，使用cast(数字 as varchar)+'字符串’的形式，直接相加不可用

--定义int类型参数i
declare @i int 
set @i=2013
while @i<2021 --进行循环，while  begin  end
begin
    insert into envi_zhishu_emi_day_data
	SELECT station_num, sdate,avg(emi_index) as emi_index
	FROM envi_zhishu_emi_hour_data d,
	(
	select observedate sdate,DATEADD(hour,23,observedate) edate from (
	SELECT
		DATEADD(DAY,number,CAST(@i as varchar)+'-01-01 00:00:00') observedate
	FROM
		master..spt_values 
	WHERE
		type = 'p'
	AND number<=DATEDIFF(day,CAST(@i as varchar)+'-01-01 00:00:00',CAST(@i as varchar)+'-12-31 00:00:00')
	) x
	) y	
	 where date_time>=sdate and date_time<=edate group by station_num,sdate
	set @i=@i+1
end