因为查询出来的是字符: 比如 select 'a,b' from tablename 你看看输出什么用EXEC ()执行就可以了
即使是分开写,用geodetic的方法:declare @colname varchar(50) select @colname=select syscolumns.name from syscolumns where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName' exec('select '+@colname+ ' from table1')也是抱错啊!提示‘Incorrect syntax near the keyword select’
参考这个:declare @sql nvarchar(1000) set @sql='select (select quotename(syscolumns.name , '''') from syscolumns where syscolumns.id = object_id(''table1'')) from table1 where quotename(syscolumns.name , '''') = ''tName''' exec (@sql)
你的另一贴有回这个: 你看不行还是怎么了? declare @sql varchar(max) set @sql='select quotename(syscolumns.name , '''') from syscolumns where syscolumns.id = object_id(''table1'')' exec (@sql)
这个只能返回列名,难道SQL没有办法根据列名返回列值么?
--引入中间变量 declare @colname varchar(50) select @colname=select syscolumns.name from syscolumns where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName' exec('select '+@colname+ ' from table1')
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'select'.
select (select syscolumns.name from syscolumns where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName' ) from table1 带颜色的这个子查询只返回 name 的集合。
--更正 --引入中间变量 declare @colname varchar(50) select @colname= syscolumns.name from syscolumns where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName' exec('select '+@colname+ ' from table1')
多谢水族杰綸,现在这个查询可以了,但是还剩下一个问题,就是 AND syscolumns.name = 'tName' 这里的tName是变量,我前天问了一个问题,也是您给出的解答:http://topic.csdn.net/u/20081106/01/bbcc8e92-4206-4d04-a59f-f96ac92aa180.html您给的语句是:set nocount on if object_id('tb') is not null drop table tb go create table tb(id int , bool1 varchar(10), bool2 varchar(10), bool3 varchar(10)) insert tb select 1, 'true', 'false', 'false' insert tb select 2, 'false', 'true', 'flase' insert tb select 3, 'true', 'false', 'false'declare @sql varchar(8000) --set @sql='select * from (' select @sql = isnull(@sql + ' union all ' , '' ) + ' select id , [booltrue] = case when ' + quotename(Name , '') + '=''true'' then'+quotename(Name , '''')+ ' end from tb ' from syscolumns where name!=N'ID' and id=object_id('tb') order by colid asc set @sql='select * from (' +@sql +') t where booltrue is not null' exec(@sql + ' order by id ') /*id booltrue ----------- -------- 1 bool1 2 bool2 3 bool1 */ 现在这个tName其实就是booltrue,我想把这两个查询连接起来,也就是说:怎样在 AND syscolumns.name = 'tName' 这条语句把tName换成booltrue列的值?谢谢!
这个booltrue是生成的别名,是不会在syscolumns中生成的
declare @s varchar(8000),@colname varchar(100) select @colname=syscolumns.name from syscolumns where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName' set @s='select '+@colname +' from table1' exec(@s)
select @colname=select syscolumns.name
from syscolumns
where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName'
exec('select '+@colname+ ' from table1')
似乎没有办法略过@colname的定义(字符串不能和select的结果直接相加)
1, zhao
2, qian
3, sun
... Table1:
tId,zhao, qian, sun...
1, a, b, c
2, d, e, f
我想查找的结果是:
tId,value
1, a
2, e 所以用到: declare @sql nvarchar(1000)
set @sql='select (select quotename(syscolumns.name , '''') from syscolumns where syscolumns.id = object_id(''table1'')) from table1 where quotename(syscolumns.name , '''') = ''tName'''
exec (@sql) 我发现其他方法不可以因为table2是动态的,是查询结果,不是一个简单的表。
参考
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html
倒数第二,第三个例子。
因为查询出来的是字符: 比如 select 'a,b' from tablename 你看看输出什么用EXEC ()执行就可以了
select @colname=select syscolumns.name
from syscolumns
where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName'
exec('select '+@colname+ ' from table1')也是抱错啊!提示‘Incorrect syntax near the keyword select’
set @sql='select (select quotename(syscolumns.name , '''') from syscolumns where syscolumns.id = object_id(''table1'')) from table1 where quotename(syscolumns.name , '''') = ''tName'''
exec (@sql)
你看不行还是怎么了? declare @sql varchar(max)
set @sql='select quotename(syscolumns.name , '''') from syscolumns where syscolumns.id = object_id(''table1'')'
exec (@sql)
declare @colname varchar(50)
select @colname=select syscolumns.name
from syscolumns
where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName'
exec('select '+@colname+ ' from table1')
Incorrect syntax near the keyword 'select'.
带颜色的这个子查询只返回 name 的集合。
--引入中间变量
declare @colname varchar(50)
select @colname= syscolumns.name
from syscolumns
where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName'
exec('select '+@colname+ ' from table1')
if object_id('tb') is not null drop table tb
go
create table tb(id int , bool1 varchar(10), bool2 varchar(10), bool3 varchar(10))
insert tb select 1, 'true', 'false', 'false'
insert tb select 2, 'false', 'true', 'flase'
insert tb select 3, 'true', 'false', 'false'declare @sql varchar(8000)
--set @sql='select * from ('
select @sql = isnull(@sql + ' union all ' , '' ) + ' select id , [booltrue] = case when ' + quotename(Name , '') + '=''true'' then'+quotename(Name , '''')+ ' end from tb '
from syscolumns
where name!=N'ID' and id=object_id('tb')
order by colid asc
set @sql='select * from (' +@sql +') t where booltrue is not null'
exec(@sql + ' order by id ')
/*id booltrue
----------- --------
1 bool1
2 bool2
3 bool1
*/
现在这个tName其实就是booltrue,我想把这两个查询连接起来,也就是说:怎样在 AND syscolumns.name = 'tName' 这条语句把tName换成booltrue列的值?谢谢!
select @colname=syscolumns.name from syscolumns where syscolumns.id = object_id('table1') AND syscolumns.name = 'tName'
set @s='select '+@colname +' from table1'
exec(@s)
http://topic.csdn.net/u/20081108/20/03764b8d-7d7d-40eb-93ad-eaa90b028718.html