SQL 是否可以使用列序号位置选择sql server数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/368505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to select sql server data using column ordinal position
提问by rams
Is it possible to select column data using the ordinal_position for a table column? I know using ordinal positions is a bad practice but for a one-off data import process I need to be able to use the ordinal position to get the column data.
是否可以使用表列的 ordinal_position 选择列数据？我知道使用序数位置是一种不好的做法，但对于一次性数据导入过程，我需要能够使用序数位置来获取列数据。
So for example
所以例如
create table Test(
Col1 int,
Col2 nvarchar(10)
)
instead of using
而不是使用
select Col2 from Test
can I write
我可以写吗
select "2" from Test -- for illustration purposes only
回答by Booji Boy
You'd have to do something like
你必须做类似的事情
declare @col1 as varchar(128)
declare @col2 as varchar(128)
declare @sq1 as varchar(8000)
select @col1 = column_name from information_schema.columns where table_name = 'tablename'
and ordinal_position = @position
select @col2 = column_name from information_schema.columns where table_name = 'tablename'
and ordinal_position = @position2
set @sql = 'select ' + col1 ',' + col2 'from tablename'
exec(@sql)
回答by Pavel Sinkevich
If you know quantity of columns, but don't know its names and types, you can use following trick:
如果您知道列的数量，但不知道其名称和类型，则可以使用以下技巧：
select NULL as C1, NULL as C2 where 1 = 0
-- Returns empty table with predefined column names
union all
select * from Test
-- There should be exactly 2 columns, but names and data type doesn't matter
As a result, you will have a table with 2 columns [C1] and [C2]. This method is not very usefull if you have 100 columns in your table, but it works well for tables with small predefined number of columns.
因此，您将拥有一个包含 2 列 [C1] 和 [C2] 的表格。如果您的表中有 100 列，则此方法不是很有用，但它适用于预定义列数较少的表。
回答by Booji Boy
You can use this query
您可以使用此查询
select * from information_schema.columns
to get the ordinal positions of the columns. Like Michael Haren wrote, you'll have to build a dynamic query using this, either in code or in a sproc that you pass the column positions to.
获取列的顺序位置。就像 Michael Haren 写的那样，您必须使用它构建一个动态查询，无论是在代码中还是在您将列位置传递给的 sproc 中。
FWIW, this is pure evil.
FWIW，这是纯粹的邪恶。
Also, deathofrats is right, you can't really do this, since you'll be building a regular query w/ column names based on position.
此外，deathofrats 是对的，您不能真正做到这一点，因为您将根据位置构建带有列名的常规查询。
回答by Michael Haren
Yes, you could do this with some really ugly hits into the system tables. You'd probably need to fall into the world of dynamic sql.
是的，您可以通过对系统表进行一些非常难看的点击来做到这一点。您可能需要进入动态 sql 的世界。
I really, really do not recommend this approach.
我真的，真的不推荐这种方法。
If that didn't deter you, then this might get you started (ref):
如果这没有阻止你，那么这可能会让你开始（ref）：
select table_name, column_name, ordinal_position, data_type
from information_schema.columns
order by 1,3
回答by Eric Minkes
No, you can't select columns based on their ordinal position, as far as I know.
不，据我所知，您不能根据它们的顺序位置选择列。
When looking at the transact SQL reference, there is nothing to suggest you can (http://msdn.microsoft.com/en-us/library/ms176104(SQL.90).aspx).
查看事务 SQL 参考时，没有任何建议您可以（http://msdn.microsoft.com/en-us/library/ms176104(SQL.90).aspx）。
回答by Damien Goor
An option you have is using conditions: In your example:
您可以选择使用条件：在您的示例中：
SELECT
CASE YourColumnNumber
WHEN "1" THEN Col1
WHEN "2" THEN Col2
ELSE "?"
END AS Result
FROM Test
Going to schema will slow query I am afraid...
去架构会减慢查询我恐怕...
回答by craigl
If you know the number of columns, one way might be to transfer the data into a tmp table with that number of columns and select from the temp table...
如果您知道列数，一种方法可能是将数据传输到具有该列数的 tmp 表中，然后从临时表中选择...
declare @tmp table(field1 sql_variant, field2 int, field3 sql_variant)
insert into @tmp
select * from Test
select field2 from @tmp
回答by Tom H
I don't know of any way to do this short of using dynamic SQL. Maybe if you include a bit more information about why you feel you have to use the ordinal values someone on here can give you advice on how to get around that problem.
除了使用动态 SQL，我不知道有什么方法可以做到这一点。也许如果您包含更多关于为什么您觉得必须使用序数值的信息，这里有人可以为您提供有关如何解决该问题的建议。
EDIT: I see that you answered this to some degree in another comment. Can you provide more specifics? The import proc and/or table definitions?
编辑：我看到你在另一条评论中在某种程度上回答了这个问题。你能提供更多的细节吗？导入过程和/或表定义？
回答by robsoft
I don't think you can. As @Michael Haren showed, you can use ordinal positions in ORDER BY clauses but I've never seen them used elsewhere in SQL Server.
我不认为你可以。正如@Michael Haren 所示，您可以在 ORDER BY 子句中使用序数位置，但我从未见过它们在 SQL Server 的其他地方使用过。
I'm not sure what problem you are havng with your one-off data import that this would help with - presumably some unfortunate column name? Can you explain a little more?
我不确定您的一次性数据导入有什么问题，这会有所帮助 - 大概是一些不幸的列名？你能再解释一下吗？
回答by user17670
If you are using MS SQL 2005 you can use the ROW_NUMBER function.
如果您使用的是 MS SQL 2005，则可以使用 ROW_NUMBER 函数。
SELECT Col1, Col2, ROW_NUMBER() OVER(ORDER BY Col1) FROM Test WHERE ROW_NUMBER() Over(Order BY Col1) Between @Position AND @Position
SELECT Col1, Col2, ROW_NUMBER() OVER(ORDER BY Col1) FROM Test WHERE ROW_NUMBER() Over(Order BY Col1) 在@Position 和@Position 之间
That should get you the desired results if I am reading the question correctly.
如果我正确阅读问题，那应该会得到你想要的结果。
