不显示第1,2列:
declare @s int
declare @i int
declare @sql varchar(300)
select @s = count(*) from syscolumns as A left join sysobjects as B
on A.id = B.id
where B.name = 'a1'
set @sql = 'select ""'
if @s > 2
begin
set @i = 3
while exists (select * from syscolumns as A left join sysobjects as B
on A.id = B.id where B.name = 'a1' and A.colorder = @i )
begin
select @sql = @sql + ',' + A.name from syscolumns as A left join sysobjects as B
on A.id = B.id where B.name = 'a1' and A.colorder = @i
set @i = @i +1
end
end
set @sql = @sql + ' from a1'
exec (@sql)
declare @s int
declare @i int
declare @sql varchar(300)
select @s = count(*) from syscolumns as A left join sysobjects as B
on A.id = B.id
where B.name = 'a1'
set @sql = 'select ""'
if @s > 2
begin
set @i = 3
while exists (select * from syscolumns as A left join sysobjects as B
on A.id = B.id where B.name = 'a1' and A.colorder = @i )
begin
select @sql = @sql + ',' + A.name from syscolumns as A left join sysobjects as B
on A.id = B.id where B.name = 'a1' and A.colorder = @i
set @i = @i +1
end
end
set @sql = @sql + ' from a1'
exec (@sql)
set @=''
select @=@+rtrim(name)+',' from syscolumns where id=object_id('你的表名') and name<>'你不要的列名'
set @=left(@,len(@)-1)
exec('select '+@+' from 你的表名')
也可以用系统函数:declare @sql nvarchar(4000),@i int
select @i=1,@sql='select 'while col_name(object_id('tablename'),@i) is not null
begin
if @i<>1 or @i<>2
set @sql=@sql+col_name(object_id('tablename'),@i)+','
endset @sql=left(@sql,len(@sql)-1)+' from tablename'
exec(@sql)
if @i<>1 and @i<>2
select @i=1,@sql='select 'while col_name(object_id('tablename'),@i) is not null
begin
if @i<>1 and @i<>2
set @sql=@sql+col_name(object_id('tablename'),@i)+','
set @i=@i+1
endset @sql=left(@sql,len(@sql)-1)+' from tablename'
exec(@sql)
引申:有两个表a,b 要取a的全部列
想把b的某字段插到a中某一位置,即在某两列之间:
select a.*,b.c1
from a inner join b on... ===> select a.c1,a.c2,..b.c1, a.某列,...
from a inner join b on...该如何?