SQL:怎样将多个表的查询结果放到一张表里?该如何解决
SQL:怎样将多个表的查询结果放到一张表里?
思路是这样的:
现在有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
------解决方案--------------------
没明白意思
------解决方案--------------------
思路是这样的:
现在有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
------解决方案--------------------
没明白意思
------解决方案--------------------
- 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 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);
------解决方案--------------------
- SQL code
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 */