SELECT * FROM( select *,CNT=row_number() over(order by getdate()) from tb ) WHERE CNT=2
列出表里的所有的列 select name from syscolumns where id=object_id('TableName')
--2000 ALTER TABLE TB ADD ID INT IDENTITY GO SELECT * FROM TB WHERE ID=2 GO ALTER TABLE TB DROP COLUMN ID
这个要用SYSCOLUMNS+ROW_NUMBER()做
select * from tb 然后在程序中引用的时候ds.table[0].row[0][i](net平台下),其中i就是你要得到的列的结果列;
DECLARE @S VARCHAR(10) SELECT @S=ISNULL(@S+',','')+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB') AND COLID IN(2,7) EXEC('SELECT '+@S+' FROM TB')
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB( COL8 INT, COL2 INT, COL3 INT, COL4 INT, COL5 INT, COL7 INT, COL1 INT, COL6 INT ) INSERT INTO TB SELECT 1,2,3,4,5,6,7,8 DECLARE @SQL AS VARCHAR(8000) SELECT @SQL='SELECT ' SELECT @SQL=ISNULL(@SQL,'SELECT ')+[NAME]+',' FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ID ) 'ROW',[NAME] FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB') ) T WHERE ROW IN (2,7) SET @SQL=LEFT(@SQL,LEN(@SQL)-1)+' FROM TB' EXEC(@SQL) /* COL2 COL1 2 7 */
select top1 name from( SELECT top 3 name FROM tb order by name desc)
--字符串短了DECLARE @S VARCHAR(100) SELECT @S=ISNULL(@S+',','')+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB') AND COLID IN(2,7) EXEC('SELECT '+@S+' FROM TB)
--刚才没写好 select name from syscolumns where id=object_id('Categories') and colid in(2,7)
select name from syscolumns where id=object_id('TB') and colid in(1,2)
--order by 可以按列顺序排序,如: select * from orders order by 2--查询不行,可以先得到列名,再进行查询 declare @column nvarchar(50),@sql nvarchar(4000) select @column=COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='orders' and ORDINAL_POSITION =5
select @sql='select '+@column +' from orders'exec sp_executesql @sql
Select name From syscolumns Where id = object_id('[TABLENAME]') and colid IN (2,7)
修改一下水族的: SELECT * FROM( select *,CNT=row_number() over(order by getdate()) from tb ) WHERE CNT in (2,7)
-->>>>soft_wsx>>>>-- declare @sql nvarchar(1000) set @sql=N'' select @sql=@sql+N','+name --从系统表中找出2至7列的数据 from syscolumns where id=object_id('spkfk') and colid between 2 and 7 set @sql=stuff(@sql,1,1,N'') --删除@SQL最前面的,号 print @sql --spbh,sptm,spmch,zjm,beactive,yishj exec(N'select '+@sql+N' from spkfk')--WHERE条件 通过测试
DECLARE @S VARCHAR(100) SELECT @S=ISNULL(@S+',','')+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TableName') AND COLID IN(2,7) EXEC('SELECT '+@S+' FROM TableName')这个
select *,CNT=row_number() over(order by getdate()) from tb
)
WHERE CNT=2
select name from syscolumns where id=object_id('TableName')
ALTER TABLE TB
ADD ID INT IDENTITY
GO
SELECT * FROM TB WHERE ID=2
GO
ALTER TABLE TB
DROP COLUMN ID
SELECT @S=ISNULL(@S+',','')+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB') AND COLID IN(2,7)
EXEC('SELECT '+@S+' FROM TB')
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
COL8 INT,
COL2 INT,
COL3 INT,
COL4 INT,
COL5 INT,
COL7 INT,
COL1 INT,
COL6 INT
)
INSERT INTO TB
SELECT 1,2,3,4,5,6,7,8
DECLARE @SQL AS VARCHAR(8000)
SELECT @SQL='SELECT '
SELECT @SQL=ISNULL(@SQL,'SELECT ')+[NAME]+',' FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ID ) 'ROW',[NAME] FROM SYSCOLUMNS
WHERE ID=OBJECT_ID('TB')
) T WHERE ROW IN (2,7)
SET @SQL=LEFT(@SQL,LEN(@SQL)-1)+' FROM TB'
EXEC(@SQL)
/*
COL2 COL1
2 7
*/
SELECT top 3 name FROM tb
order by name desc)
SELECT @S=ISNULL(@S+',','')+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB') AND COLID IN(2,7)
EXEC('SELECT '+@S+' FROM TB)
select name from syscolumns where id=object_id('Categories') and colid in(2,7)
select * from orders order by 2--查询不行,可以先得到列名,再进行查询
declare @column nvarchar(50),@sql nvarchar(4000)
select @column=COLUMN_NAME from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='orders' and ORDINAL_POSITION =5
select @sql='select '+@column +' from orders'exec sp_executesql @sql
Select name From syscolumns Where id = object_id('[TABLENAME]') and colid IN (2,7)
SELECT * FROM( select *,CNT=row_number() over(order by getdate()) from tb ) WHERE CNT in (2,7)
-->>>>soft_wsx>>>>--
declare @sql nvarchar(1000)
set @sql=N''
select @sql=@sql+N','+name --从系统表中找出2至7列的数据
from syscolumns where id=object_id('spkfk') and colid between 2 and 7
set @sql=stuff(@sql,1,1,N'') --删除@SQL最前面的,号
print @sql --spbh,sptm,spmch,zjm,beactive,yishj
exec(N'select '+@sql+N' from spkfk')--WHERE条件
通过测试
SELECT @S=ISNULL(@S+',','')+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TableName') AND COLID IN(2,7)
EXEC('SELECT '+@S+' FROM TableName')这个