3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
wzh1215 2003-12-26
--少了点东西:
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from
(
select top 10 a.name from syscolumns a where id=object_id('xxx') and
a.name not in(
select top 10 name from syscolumns where id=object_id('xxx') order by len(name),name
)
order by len(name),name
) tem
select @sql=right(@sql,len(@sql)-1)
select @sql='select '+@sql+' from xxx'
select @sql
exec (@sql)
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from
(
select top 10 a.name from syscolumns a where id=object_id('xxx') and
a.name not in(
select top 10 name from syscolumns where id=object_id('xxx') order by len(name),name
)
order by len(name),name
) tem
select @sql=right(@sql,len(@sql)-1)
select @sql='select '+@sql+' from xxx'
select @sql
exec (@sql)
cxfirefox 2003-12-26
To wzh1215(四脚蛇)：按照这句修改的话
select top 10 a.name from syscolumns a where id=object_id('xxx') and a.name not in(select top 10 name from syscolumns order by len(name),name) order by len(name),name
如果我把not in里面的10修改为0，则从头开始读起，如果修改为2，3，4，都是从第二个字段读起，为什么不能继续往后，从任意一个字段读起呢？
select top 10 a.name from syscolumns a where id=object_id('xxx') and a.name not in(select top 10 name from syscolumns order by len(name),name) order by len(name),name
如果我把not in里面的10修改为0，则从头开始读起，如果修改为2，3，4，都是从第二个字段读起，为什么不能继续往后，从任意一个字段读起呢？
gmlxf 2003-12-26
如果要更改表的名字应该改哪里呢？
--------------------------------
select @n = 5,@tName = 'authors' -- authors是你的表
--------------------------------
select @n = 5,@tName = 'authors' -- authors是你的表
wzh1215 2003-12-26
select top 10 a.name from syscolumns a where id=object_id('xxx') order by len(name),name
只要把这面这句改一下就行了：
select top 10 a.name from syscolumns a where id=object_id('xxx') and a.name not in(select top 10 name from syscolumns order by len(name),name) order by len(name),name
------------------
最好还是没这样用，不就十个字段嘛。字段多时再考虑用下面的！
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from
syscolumns a where id=object_id('xxx') and name not in(col1,col2,...这里为你不要的字段列表) tem
select @sql=right(@sql,len(@sql)-1)
select @sql='select '+@sql+' from xxx'
select @sql
exec (@sql)
只要把这面这句改一下就行了：
select top 10 a.name from syscolumns a where id=object_id('xxx') and a.name not in(select top 10 name from syscolumns order by len(name),name) order by len(name),name
------------------
最好还是没这样用，不就十个字段嘛。字段多时再考虑用下面的！
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from
syscolumns a where id=object_id('xxx') and name not in(col1,col2,...这里为你不要的字段列表) tem
select @sql=right(@sql,len(@sql)-1)
select @sql='select '+@sql+' from xxx'
select @sql
exec (@sql)
cxfirefox 2003-12-26
To wzh1215(四脚蛇)：能不能从中间选取一部分字段呢？比如说从第十个开始，选择10 个？
wzh1215 2003-12-26
--再改一改，这样选出来的恐怕很难符合你的要求！
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from
(
select top 10 a.name from syscolumns a where id=object_id('xxx') order by len(name),name
) tem
select @sql=right(@sql,len(@sql)-1)
select @sql='select '+@sql+' from xxx'
select @sql
exec (@sql)
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from
(
select top 10 a.name from syscolumns a where id=object_id('xxx') order by len(name),name
) tem
select @sql=right(@sql,len(@sql)-1)
select @sql='select '+@sql+' from xxx'
select @sql
exec (@sql)
cxfirefox 2003-12-26
to gmlxf(烛光)：如果要更改表的名字应该改哪里呢？如果是要从中间选择一部分字段，应该怎么写呢？
wzh1215 2003-12-26
create table xxx(col1 int,col2 int,col3 int,col4 int,col5 int,col6 int,col7 int,col8 int,col9 int,col10 int,col11 int,col12 int)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,1)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,2)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,3)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,4)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,5)
go
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from (
select top 10 a.name from syscolumns a where id=object_id('xxx')
) tem
select @sql=right(@sql,len(@sql)-1)
select @sql='select '+@sql+' from xxx'
select @sql---仔细地看看这里运行的结果！
exec (@sql)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,1)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,2)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,3)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,4)
insert xxx values(1,1,1,1,1,1,1,1,1,1,1,5)
go
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from (
select top 10 a.name from syscolumns a where id=object_id('xxx')
) tem
select @sql=right(@sql,len(@sql)-1)
select @sql='select '+@sql+' from xxx'
select @sql---仔细地看看这里运行的结果！
exec (@sql)
gmlxf 2003-12-26
-- 将以下代码贴到查询分析器执行，它的功能是查询pubs..authors这个表中的前五个字段的所有信息：
use pubs
declare @n tinyint,@sql varchar(8000),@tName sysname
select @n = 5,@tName = 'authors'
set @sql = 'select top ' + cast(@n as varchar) + ' '
select top 5 @sql = @sql + name + ',' from syscolumns where object_id(@tName) = id
select @sql = left(@sql,len(@sql) - 1) + ' from ' + @tName
-- print @sql
exec(@sql)
use pubs
declare @n tinyint,@sql varchar(8000),@tName sysname
select @n = 5,@tName = 'authors'
set @sql = 'select top ' + cast(@n as varchar) + ' '
select top 5 @sql = @sql + name + ',' from syscolumns where object_id(@tName) = id
select @sql = left(@sql,len(@sql) - 1) + ' from ' + @tName
-- print @sql
exec(@sql)
cxfirefox 2003-12-26
to wzh1215(四脚蛇):不好意思，我不是太懂，能不能举个例子，比如说table Psx,有100个子段，分别是a1到a100，上面的那些应该怎么翻译呢？
cxfirefox 2003-12-26
不是这样，是只选择前十个字段，而不是前十个记录，本来可以select 字段1，字段2…… from table,但是现在我想问的是有没有办法不列举这些字段名来选择！
shuiniu 2003-12-26
必需写！
－－－－－
要不这样！
declare @sql varchar(100)
set @sql = ''
select @sql =@sql + name + ',' from syscolumns where id = object_id('表名') and colid < 11
set @sql = left(@sql,len(@sql) -1)
exec('select ' + @sql + ' from 表名' )
－－－－－
要不这样！
declare @sql varchar(100)
set @sql = ''
select @sql =@sql + name + ',' from syscolumns where id = object_id('表名') and colid < 11
set @sql = left(@sql,len(@sql) -1)
exec('select ' + @sql + ' from 表名' )
wzh1215 2003-12-26
--方法是有，不过还不如不用：
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from (
select top 10 a.name from syscolumns a,sysobjects b where a.id=b.id and b.name='你的表'
) tem
select @sql=right(@sql,len(@sql)-1)
select @sql
exec ('select '+@sql+' from 你的表')
declare @sql varchar(100)
select @sql=''
select @sql=@sql+','+name from (
select top 10 a.name from syscolumns a,sysobjects b where a.id=b.id and b.name='你的表'
) tem
select @sql=right(@sql,len(@sql)-1)
select @sql
exec ('select '+@sql+' from 你的表')
gmlxf 2003-12-26
--??什么意思，选出来什么用？
select top 10 name from syscolumns where object_id('tname') = id
select top 10 name from syscolumns where object_id('tname') = id
