SQL:怎样将多个表的查询结果放到一张表里?

[comcyd]

思路是这样的:
现在有A表,B表,C表:
A:
a01 a02
--------
1 11
2 22

B:
b01 b02
--------
10 101
20 201

C表:
c01 c02
--------
A xx
B xx

现要想取得表A和表B(表A和表B的表名来自表C查询出来的第一个c01字段)中的前两个字段的数据即a01,a02;b01,b02字段并融合成一张表tmp里,如下显示:
tmp
t01 t02
--------
a01 a02
b01 b02

[simonxt]

没明白意思

[comcyd]

最后那里写错了,应该是:

现要想取得表A和表B(表A和表B的表名来自表C查询出来的第一个c01字段)中的前两个字段的数据即a01,a02;b01,b02字段并融合成一张表tmp里,如下显示:
tmp
t01 t02
--------
1 11
2 22
10 101
20 201

[JM]

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);

[bala7229291]

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
*/

[comcyd]

这个报错找不到表C[Quote=引用 3 楼 的回复:]

SQL code
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 fro……
[/Quote]

[comcyd]

不好意思,我忘了是用我给出的表了,你的答案也是OK的[Quote=引用 3 楼 的回复:]

SQL code
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 fro……
[/Quote]

[comcyd]

上面的都对