select year(getdate())-10 union all select year(getdate())-9 union all select year(getdate())-8 union all select year(getdate())-7 union all select year(getdate())-6 union all select year(getdate())-5 union all select year(getdate())-4 union all select year(getdate())-3 union all select year(getdate())-2 union all select year(getdate())-1 union all select year(getdate()) union all select year(getdate())+1 union all select year(getdate())+2 union all select year(getdate())+3 union all select year(getdate())+4 union all select year(getdate())+5 union all select year(getdate())+6 union all select year(getdate())+7 union all select year(getdate())+8 union all select year(getdate())+9 union all select year(getdate())+10
-- 创建表值函数 create function dbo.fn_GetYear (@Year int) returns @tb table([year] int) as begin declare @i int set @i=@Year-10 while(@i<@Year+10) begin insert @tb select @i set @i=@i+1 end return end Go-- 查询 select * from dbo.fn_GetYear(2007) Go
declare @t1 int,@year1 varchar(4),@year2 varchar(4),@sql varchar(8000) set @sql='' set @year1=year(getdate()) set @t1=-10 while @t1>=-10 and @t1<=10 begin set @year2=@year1+@t1 set @sql=@sql+'select '+@year2+' [year] union all ' set @t1=@t1+1 endset @sql=substring(@sql,1,len(@sql)-10)+'order by [year] ' print substring(@sql,1,len(@sql)-10) exec (@sql) ---------- year 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017
declare @i int declare @str varchar(8000) set @i=-10 set @str='' while @i<=10 begin set @str=@str+'union all select year(getdate())+('+cast(@i as varchar(10))+')' set @i=@i+1 end exec(stuff(@str,1,10,'')) /* 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 */
declare @i int declare @str varchar(8000) set @i=-10 set @str='' while @i<=10 begin set @str=@str+'union all select year(getdate())+('+cast(@i as varchar(10))+')' set @i=@i+1 end set @str=stuff(@str,1,10,'') exec(@str)
--改进一下mschen(Co-ok)的(少了一年) --加多一个参数:前后的年数--创建函数 create function dbo.fn_GetYear(@Year int,@Num int) returns @tb table([Year] int) as begin declare @i int set @i=@Year-@Num while(@i<=@Year+@Num) begin insert @tb select @i set @i=@i+1 end return end go--调用函数 select * from dbo.fn_GetYear(2007,2) go--删除环境 drop function fn_GetYear--结果 /* Year 2005 2006 2007 2008 2009 */
union all 1999
...
union all 2017
select year(getdate())-9 union all
select year(getdate())-8 union all
select year(getdate())-7 union all
select year(getdate())-6 union all
select year(getdate())-5 union all
select year(getdate())-4 union all
select year(getdate())-3 union all
select year(getdate())-2 union all
select year(getdate())-1 union all
select year(getdate()) union all
select year(getdate())+1 union all
select year(getdate())+2 union all
select year(getdate())+3 union all
select year(getdate())+4 union all
select year(getdate())+5 union all
select year(getdate())+6 union all
select year(getdate())+7 union all
select year(getdate())+8 union all
select year(getdate())+9 union all
select year(getdate())+10
create function dbo.fn_GetYear
(@Year int)
returns @tb table([year] int)
as
begin
declare @i int
set @i=@Year-10
while(@i<@Year+10)
begin
insert @tb select @i
set @i=@i+1
end
return
end
Go-- 查询
select * from dbo.fn_GetYear(2007)
Go
set @sql=''
set @year1=year(getdate())
set @t1=-10 while @t1>=-10 and @t1<=10
begin
set @year2=@year1+@t1
set @sql=@sql+'select '+@year2+' [year] union all '
set @t1=@t1+1
endset @sql=substring(@sql,1,len(@sql)-10)+'order by [year] '
print substring(@sql,1,len(@sql)-10)
exec (@sql)
----------
year
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
declare @str varchar(8000)
set @i=-10
set @str=''
while @i<=10
begin
set @str=@str+'union all select year(getdate())+('+cast(@i as varchar(10))+')'
set @i=@i+1
end
exec(stuff(@str,1,10,''))
/*
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
*/
declare @str varchar(8000)
set @i=-10
set @str=''
while @i<=10
begin
set @str=@str+'union all select year(getdate())+('+cast(@i as varchar(10))+')'
set @i=@i+1
end
set @str=stuff(@str,1,10,'')
exec(@str)
--加多一个参数:前后的年数--创建函数
create function dbo.fn_GetYear(@Year int,@Num int)
returns @tb table([Year] int)
as
begin
declare @i int
set @i=@Year-@Num
while(@i<=@Year+@Num)
begin
insert @tb select @i
set @i=@i+1
end
return
end
go--调用函数
select * from dbo.fn_GetYear(2007,2)
go--删除环境
drop function fn_GetYear--结果
/*
Year
2005
2006
2007
2008
2009
*/