大侠给看看用sql取当前年的前后10年的一个查询,急!
例如今年2007年,用sql取一个查询结果为:
列名A
1998
1999
2000
...
2007
2008
2017
[解决办法]
declare @t1 int,@t2 int,@year1 varchar(4),@year2 varchar(4),@sql varchar(8000)
set @sql= ' '
set @year1=year(getdate())
set @t1=0
while @t1 <=10
begin
set @year2=@year1+@t1
set @sql=@sql+ 'select '+@year2+ ' [year] union all '
set @t1=@t1+1
end
set @t2=-10
while @t2 <=-1
begin
set @year2=@year1+@t2
set @sql=@sql+ 'select '+@year2+ ' [year] union all '
set @t2=@t2+1
end
set @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
end
set @sql=substring(@sql,1,len(@sql)-10)+ 'order by [year] '
print substring(@sql,1,len(@sql)-10)
exec (@sql)
