Select問題
設有一表A
有100個字段
如果我想Select除字段AID外的99個字段
如何寫Select 語句呢
[解决办法]
--列出所有字段
declare @s varchar(8000)
set @s= ' '
select @s=@s+name+ ', ' from syscolumns
where id=object_id( '表名 ') and name <> 'id '
order by colid
print left(@s,len(@s)-1)
--select所有字段
declare @s varchar(8000)
set @s= ' '
select @s=@s+ ' ' ' '+name+ ' ' ', ' from syscolumns
where id=object_id( '表名 ') and name <> 'id '
order by colid
set @s=left(@s,len(@s)-1)
exec( 'select '+@s)
[解决办法]
declare @sql varchar(8000)
select @sql = coalesce(@sql + ', ', ' ') + name from syscolumns where id = object_id( '表A ') and name <> 'AID '
set @sql = 'select ' + @sql + ' from 表A '
print @sql
[解决办法]
mark
[解决办法]
select @sql = coalesce(@sql + ', ', ' ')
------------------------------------------
路过，
从 Limpire(昨夜小楼) 那 学习了新方法。
[解决办法]
mark
[解决办法]
得到表中除Col1、Col2的所有列
例如：userno_fm、userno_to
create table test(
num int identity(1,1),
userno_fm varchar(10),
userno_to varchar(10),
username varchar(10))
select * from test
declare @sql varchar(8000)
select @sql= ' '
select @sql=@sql+ ', '+[name] from
(select [name] from syscolumns where object_id(N '[test] ')=[id] and [name] not in ( 'userno_fm ', 'userno_to ')) A
set @sql= 'select '+stuff(@sql,1,1, ' ')+ ' from [test] '
--print @sql
exec (@sql)
drop table test
[解决办法]
declare @col varchar(3000)
set @col= ' '
select @col=@col+name+ ', ' from syscolumns
where id=object_id( 'tab ') and name != 'id '
order by colid
set @col=left(@col,len(@col)-1)
exec( 'select '+@col+ ' from tab ')
