日期:2014-05-18 浏览次数:20584 次
--根据查询的列创建一个临时表 create table table_111201(id int) insert into table_111201 select 1 go create table table_111202(id int) insert into table_111201 select 2 go create table #temp(id int) -- 具体的字段 declare @start datetime declare @end datetime declare @sql varchar(8000) set @start = '2011-12-01' set @end = '2011-12-02' select @sql = isnull(@sql+' union all ','')+'select * from table_'+date from( select right(convert(varchar(8),dateadd(dd,number,@start),112),6) date from master..spt_values where [type] = 'p' and number between 0 and datediff(dd,@start,@end) )t insert into #temp exec(@sql) select * from #temp --select 其他查询,根据#temp临时表 drop table #temp,table_111201,table_111202 /*************** id ----------- 1 2 (2 行受影响)
------解决方案--------------------
declare @dt1 datetime,@dt2 datetime declare @cellname varchar(10) declare @sql nvarchar(max) --设置起止日期 set @dt1='2012-02-01' set @dt2='2012-02-07' --设置小区名 set @cellname='Acell' --形成动态查询语句 select @sql=isnull(@sql+' union all ','') +'select * from Table_' +right(convert(varchar(8),dateadd(d,number,@dt1),112),6) +' where cellname='''+@cellname+'''' from master..spt_values where type='p' and dateadd(d,number,@dt1)<=@dt2 --执行动态查询语句 exec(@sql) /* 如果将上面这句改成 select @sql,则你会得到这样的字串(复制网格中的内容): select * from Table_120201 where cellname='Acell' union all select * from Table_120202 where cellname='Acell' union all select * from Table_120203 where cellname='Acell' union all select * from Table_120204 where cellname='Acell' union all select * from Table_120205 where cellname='Acell' union all select * from Table_120206 where cellname='Acell' union all select * from Table_120207 where cellname='Acell' 这就是你要的遍历各表的查询语句 */