34,588
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tb_1(id INT,Price SMALLMONEY,kucun INT)
INSERT tb_1 SELECT 1,33.44,20
UNION ALL SELECT 2,33.22,30
CREATE TABLE tb_2(id INT,Price SMALLMONEY,kucun INT)
INSERT tb_2 SELECT 1,3.44,20
UNION ALL SELECT 2,35.22,40
CREATE TABLE tb_3(id INT,Price SMALLMONEY,kucun INT)
INSERT tb_3 SELECT 1,83.44,22
UNION ALL SELECT 2,31.2,10
CREATE TABLE tb (A INT,B INT)
INSERT tb SELECT 1,1 UNION ALL SELECT 1,2 UNION ALL SELECT 1,3
GO
SELECT base.* FROM tb a
INNER JOIN
(SELECT *,tbid=1 FROM tb_1
UNION ALL
SELECT *,tbid=2 FROM tb_2
UNION ALL
SELECT *,tbid=3 FROM tb_3) base /*你能将表名以序号分别在tb表的b列,那么每一个序号肯定会对应到一个表名的*/
ON tbid=a.B AND base.id=a.A
/*
id price kucun tbid
1 33.4400 20 1
1 3.4400 20 2
1 83.4400 22 3
*/
DROP TABLE tb_1,tb_2,tb_3,tb
GO
[code=SQL]declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' select * from '+ OBJECT_NAME(b) + ' union all '
from (select distinct b from tablea) aa
set @sql = substring(@sql ,1, len(@sql) - 10)
exec (@sql)
[/code]刚才那个少一个空格,更正一下declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' select * from '+ OBJECT_NAME(b) + ' union all '
from (select distinct b from tablea) aa
set @sql = substring(@sql ,1, len(@sql) - 10)
exec (@sql)
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' select * from '+ OBJECT_NAME(b) + 'union all '
from (select distinct b from tablea) aa
set @sql = substring(@sql ,1, len(@sql) - 10)
exec (@sql)