现在有24个表 资料_2008_01,资料_2008_02资料_2008_12,资料_2009_01 资料_2009_02 。。资料_2009_12要求遍历这些表进行指定操作,应该怎么做啊?最好不使用游标,只用循环语句。还有 @sqlstr = 'select '''+@tablename+''' as tablename,* from '+@tablename+' where userid=''qifei'''类似这种的+号应用我还不太会,谁可以帮我介绍下吗?对于我自己的问题,我是这么做的declare @a int,@b int,@t varchar(40)
select @a=2008,@b=1
while @a<=2009
begin
while @b<=12
begin
set @t='select * from '+'资料_'+@a+'_'+@b //这段我卡住了,不会使用+号
exec(@t)
select @b=@b+1
end
select @a=@a+1
select @b=1请问我的思路正确吗?很急,求帮忙~~~
end
select @a=2008,@b=1
while @a<=2009
begin
while @b<=12
begin
set @t='select * from '+'资料_'+@a+'_'+@b //这段我卡住了,不会使用+号
exec(@t)
select @b=@b+1
end
select @a=@a+1
select @b=1请问我的思路正确吗?很急,求帮忙~~~
end
declare @a int,@b int,@t varchar(40)
select @a=2008,@b=1
while @a <=2009
begin
while @b <=12
begin
set @t='select * from '+'资料_'+cast(@a as varchar(20))+'_'+cast(@b as varchar(20)) --这段我卡住了,不会使用+号
print @t
SET @b=@b+1
end
set @a=@a+1
set @b=1
end/*select * from 资料_2008_1
select * from 资料_2008_2
select * from 资料_2008_3
select * from 资料_2008_4
select * from 资料_2008_5
select * from 资料_2008_6
select * from 资料_2008_7
select * from 资料_2008_8
select * from 资料_2008_9
select * from 资料_2008_10
select * from 资料_2008_11
select * from 资料_2008_12
select * from 资料_2009_1
select * from 资料_2009_2
select * from 资料_2009_3
select * from 资料_2009_4
select * from 资料_2009_5
select * from 资料_2009_6
select * from 资料_2009_7
select * from 资料_2009_8
select * from 资料_2009_9
select * from 资料_2009_10
select * from 资料_2009_11
select * from 资料_2009_12*/
declare @a int,@b int,@t varchar(40)
select @a=2008
while @a <=2009
begin
select @b=1
while @b <=12
begin
set @t='select * from '+'资料_'+right('00'+ltrim(@a),2)+'_'+right('00'+ltrim(@b),2)
exec(@t)
select @b=@b+1
end
select @a=@a+1
end
select @a=2008
while @a <=2009
begin
select @b=1
while @b <=12
begin
set @t='select * from '+'资料_'+ltrim(@a)+'_'+right('00'+ltrim(@b),2)
exec(@t)
select @b=@b+1
end
select @a=@a+1
end
declare @a datetime, @t varchar(40)
set @a='2008-01-01'
while @a <='2009-12-31'
begin
set @t='select * from '+'资料_'+DATENAME(yy,@a)+'_'+datename(mm,@a) --这段我卡住了,不会使用+号
print @t
SET @a=dateadd(mm,1,@a)
end
/*
select * from 资料_2008_01
select * from 资料_2008_02
select * from 资料_2008_03
select * from 资料_2008_04
select * from 资料_2008_05
select * from 资料_2008_06
select * from 资料_2008_07
select * from 资料_2008_08
select * from 资料_2008_09
select * from 资料_2008_10
select * from 资料_2008_11
select * from 资料_2008_12
select * from 资料_2009_01
select * from 资料_2009_02
select * from 资料_2009_03
select * from 资料_2009_04
select * from 资料_2009_05
select * from 资料_2009_06
select * from 资料_2009_07
select * from 资料_2009_08
select * from 资料_2009_09
select * from 资料_2009_10
select * from 资料_2009_11
select * from 资料_2009_12
*/
模糊表名的联合查询create table tz2008_1_1(id int,name varchar(50))
insert into tz2008_1_1 select 1,'a'
create table tz2008_1_2(id int,name varchar(50))
insert into tz2008_1_2 select 2,'b'
create table tz2008_1_3(id int,name varchar(50))
insert into tz2008_1_3 select 3,'c'-- 模糊表名的联合查询
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select * from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'select @sql=substring(@sql,11,len(@sql))
select @sql
exec(@sql)将tz2008_1_1那些表换成你的表:资料_2008_01什么的
select @a=2008,@b=1 ,@t=''
while @a <=2009
begin
while @b <=12
begin
set @t='select * from 资料_'+ ''+@a + ''+'_'+''+@b+'' //这段我卡住了,不会使用+号
exec(@t)
select @b=@b+1
end
select @a=@a+1
select @b=1
end
select @a=2008,@b=1 ,@t=''
while @a <=2009
begin
while @b <=12
begin
set @t='select * from 资料_'+ ''+@a + ''+'_'+''+@b+''
exec(@t)
select @b=@b+1
end
select @a=@a+1
select @b=1
end
select @a=2008,@b=1 ,@t=''
while @a <=2009
begin
while @b <=12
begin
set @t='select * from 资料_'+ ''+@a + ''+'_'+''+@b+''
exec(@t)
select @b=@b+1
end
select @a=@a+1
select @b=1
end