select A from TABLE where datediff(yy,A,getdate())=10 or datediff(yy,A,getdate())=-10
select A from TABLE where datediff(yy,A,getdate()) between -10 and 10
declare @t1 int,@t2 int,@year1 varchar(4),@year2 varchar(4),@sql varchar(8000) set @sql='' set @year1=year(getdate()) set @t1=0while @t1<=10 begin set @year2=@year1+@t1 set @sql=@sql+'select '+@year2+' [year] union all ' set @t1=@t1+1 endset @t2=-10 while @t2<=-1 begin set @year2=@year1+@t2 set @sql=@sql+'select '+@year2+' [year] union all ' set @t2=@t2+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 @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)
set @sql=''
set @year1=year(getdate())
set @t1=0while @t1<=10
begin
set @year2=@year1+@t1
set @sql=@sql+'select '+@year2+' [year] union all '
set @t1=@t1+1
endset @t2=-10
while @t2<=-1
begin
set @year2=@year1+@t2
set @sql=@sql+'select '+@year2+' [year] union all '
set @t2=@t2+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 @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)