假设A表中id=1,tablename=b，可不可以这样查询？

[saveaswu]

根据A表中该记录的tablename，这里是b，来连接查询b表中id也是1的记录？
关键是每次不一定是b表，也可能是c,d,e表等，由A表中的tablename字段决定。

[welyngj]

I'm puzzled!!

[txlicenhe]

declare @tablename varchar(20)
select @tablename = tablename from A where id = 1
exec('select * from ' + @tablename + ' where id = 1')

[pengdali]

declare @tablename varchar(100)
select @tablename=tablename from a

exec('select * from '+@tablename+' where id=1')

[txlicenhe]

或者试一下这个：
Select * from (select tablename from A where id = 1) temp where id = 1

[pengdali]

使用带一个变量的 EXECUTE 'tsql_string' 语句
这个例子显示 EXECUTE 语句如何处理动态生成的、含有变量的字符串。这个例子创建 tables_cursor 游标来保存所有用户定义表 (type = U) 的列表。



说明 此例子只用作举例。


DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
/* A @@FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor

[伍子V5]

create procdure pd_name
as
declare @id int
declare @tablename varchar(50)
select @id=id,@tablename=tablename from A where 条件
exec ('select * from '+@tablename+' where id='+@id)

[zjcxc]

用动态的SQL语句.

declare @sql varchar(8000)
select @sql='select * from ['+tablename+'] where id=1'
from A表 where id=1

exec(@sql)

[sdhdy]

declare @tablename varchar(8000)
select @tablename = tablename from A where id = 1
exec('select * from ' + @tablename + ' where id = 1')

[saveaswu]

实际上我是这个意思：
表a：
id tablename
1 b
2 c
3 d

表b：
id name
1 china

表c：
id name
2 usa

表d：
id name
3 tw

表b,c,d中的id字段与表a是关联的，现在我要列出这样的结果：

id name
1 china
2 usa
3 tw

[pengdali]

select id,(select name from b where id=a.id) from a where tablename='b'
union all
select id,(select name from c where id=a.id) from a where tablename='c'
union all
select id,(select name from d where id=a.id) from a where tablename='d'

[pengdali]

or

select a.id,b.name from a,b where a.tablename='b' and a.id=b.id
union all
select a.id,c.name from a,c where a.tablename='c' and a.id=c.id
union all
select a.id,d.name from a,d where a.tablename='d' and a.id=d.id

[saveaswu]

大力！关键是表a的tablename字段值，查询前是不知道的呀！可能是b，也有可能是x的呀！

[wzh1215]

select id,(select name from b where id=a.id) from a where tablename='b'
union
select id,(select name from c where id=a.id) from a where tablename='c'
union
select id,(select name from d where id=a.id) from a where tablename='d'

当表a中的tablename字段为b,c,d以外的字符就不用管了！

[saveaswu]

就是说：tablename必须事先知道值，否则就不行，是否？

[wzh1215]

不是，上面的查询已帮你处理了所有的tablename可能的值，不符合条件的都被排除了！

[angelgundam]

我不大明白您这个问题的意思
能说清楚点吗

[saveaswu]

多问一句，如果tablename有10甚至更多一些，如上这样查询：
select id,(select name from b where id=a.id) from a where tablename='b'
union
select id,(select name from c where id=a.id) from a where tablename='c'
union
select id,(select name from d where id=a.id) from a where tablename='d'
union
select id,(select name from d where id=a.id) from a where tablename='e'
union
select id,(select name from d where id=a.id) from a where tablename='f'
union
select id,(select name from d where id=a.id) from a where tablename='g'
union
select id,(select name from d where id=a.id) from a where tablename='h'

。。。。等等

效率会不会太低啦？我想象中好像是的。不知究竟如何？

[SuperGam]

根据A表中该记录的tablename，这里是b，来连接查询b表中id也是1的记录？
关键是每次不一定是b表，也可能是c,d,e表等，由A表中的tablename字段决定。

select b.* from b,(select tabelname from a) as aa,
)
where a.id=b.id and a.id=1;