日期:2014-05-18 浏览次数:20669 次
declare @firstTable nvarchar(10); select top(1) @firstTable=c01 from (select top(2) c01 from C order by c01 asc) as c1; declare @secondTable nvarchar(10); select top(1) @secondTable=c01 from (select top(2) * from C order by c01 desc)as c2; exec('select top(2) a01 as t01,a02 as t02 from '+ @firstTable+' union '+' select top(2) * from '+@secondTable);
------解决方案--------------------
if object_id('A') is not null drop table A; go if object_id('B') is not null drop table B; go if object_id('C') is not null drop table C; if object_id('temp') is not null drop table temp; go --创建测试表 create table A ( a01 int, a02 int ); go create table B ( b01 int, b02 int ); go create table C ( c01 nvarchar(10), c02 nvarchar(10) ); go --插入测试数据 insert into A select 1, 11 union all select 2, 22; go insert into B select 10, 101 union all select 20, 201; go insert into C select 'A', 'xx' union all select 'B', 'xx'; go --生成动态查询语句 declare @sql nvarchar(max); set @sql = ''; declare @table_name nvarchar(10); declare @i int; set @i = 0; declare cur_c cursor for select c01 from C; open cur_c; fetch next from cur_c into @table_name; while @@fetch_status = 0 begin if @i = 0 set @sql = @sql + 'select * into temp from ' + @table_name; else set @sql = @sql + ' union all select * from ' + @table_name; set @i = @i + 1; fetch next from cur_c into @table_name; end; close cur_c; deallocate cur_c; --执行动态查询语句 exec (@sql); go --检查结果 select * from temp; go /* a01 a02 ----------- ----------- 1 11 2 22 10 101 20 201 */