在 SQL Server 存储过程中按索引而不是名称访问表的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4882837/
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
Access columns of a table by index instead of name in SQL Server stored procedure
提问by cMinor
Is there a way to access columns by their index within a stored procedure in SQL Server?
有没有办法在 SQL Server 的存储过程中通过索引访问列？
The purpose is to compute lots of columns. I was reading about cursors, but I do not know how to apply them.
目的是计算大量列。我正在阅读有关游标的信息，但我不知道如何应用它们。
Let me explain my problem:
让我解释一下我的问题：
I have a row like:
我有一排像：
field_1 field_2 field_3 field_4 ...field_d Sfield_1 Sfield_2 Sfield_3...Sfield_n
1 2 3 4 d 10 20 30 n
I need to compute something like (field_1*field1) - (Sfield_1* Sfiled_1) / more...
我需要计算类似的东西 (field_1*field1) - (Sfield_1* Sfiled_1) / more...
So the result is stored in a table column d times.
因此结果存储在表列中 d 次。
So the result is a d column * d rowtable.
所以结果是一张d column * d row桌子。
As the number of columns is variable, I was considering making dynamic SQL, getting the names of columns in a string and splitting the ones I need, but this approach makes the problem harder. I thought getting the column number by index could make life easier.
由于列数是可变的，我正在考虑制作动态 SQL，获取字符串中的列名并拆分我需要的列名，但这种方法使问题变得更加困难。我认为通过索引获取列号可以让生活更轻松。
采纳答案by Thomas
First, as OMG Poniesstated, you cannot reference columns by their ordinal position. This is not an accident. The SQL specification is not built for dynamic schema either in DDL or DML.
首先，正如OMG Ponies所说，你不能通过它们的顺序位置来引用列。这不是意外。SQL 规范不是为 DDL 或 DML 中的动态模式构建的。
Given that, I have to wonder why you have your data structured as you do. A sign of a mismatch between schema and the problem domain rears itself when you try to extract information. When queries are incredibly cumbersome to write, it is an indication that the schema does not properly model the domain for which it was designed.
鉴于此，我不得不想知道为什么您的数据结构如此。当您尝试提取信息时，模式和问题域之间不匹配的迹象就会出现。当查询编写起来非常麻烦时，这表明模式没有正确地为它设计的域建模。
However, be that as it may, given what you have told us, an alternate solution would be something like the following: (I'm assuming that field_1*field1was meant to be field_1 * field_1or field_1squared or Power( field_1, 2 ))
但是，尽管如此，根据您告诉我们的情况，另一种解决方案将类似于以下内容：（我假设这field_1*field1应该是field_1 * field_1或field_1平方或Power( field_1, 2 )）
Select 1 As Sequence, field_1 As [Field], Sfield_1 As [SField], Sfiled_1 As [SFiled]
Union All Select 2, field_2, Sfield_2, Sfiled_2
...
Union All Select n, field_n, Sfield_n, Sfiled_n
Now your query looks like:
现在您的查询如下所示：
With Inputs As
(
Select 1 As Sequence, field_1 As [Field], Sfield_1 As [SField], Sfiled_1 As [SFiled]
Union All Select 2, field_2, Sfield_2, Sfiled_2
....
)
, Results As
(
Select Case
When Sequence = 1 Then Power( [Field], 2 ) - ( [SField] * [SFiled] )
Else 1 / Power( [Field], 2 ) - ( [SField] * [SFiled] )
End
As Result
From Inputs
)
Select Exp( Sum( Log( Result ) ) )
From Results
回答by OMG Ponies
No, you can not use the ordinal (numeric) position in the SELECTclause.
不，您不能在SELECT子句中使用序数（数字）位置。
Only in the ORDER BYclause can you use the ordinal position, because it's based on the column(s) specified in the SELECTclause.
只有在ORDER BY子句中才能使用序数位置，因为它基于SELECT子句中指定的列。
回答by Enkode
This might not be the most elegant or efficient but it works. I am using it to create a new table for faster mappings between data that I need to parse through all the columns / rows.
这可能不是最优雅或最高效的，但它确实有效。我正在使用它来创建一个新表，以便在需要解析所有列/行的数据之间进行更快的映射。
DECLARE @sqlCommand varchar(1000)
DECLARE @columnNames TABLE (colName varchar(64), colIndex int)
DECLARE @TableName varchar(64) = 'YOURTABLE' --Table Name
DECLARE @rowNumber int = 2 -- y axis
DECLARE @colNumber int = 24 -- x axis
DECLARE @myColumnToOrderBy varchar(64) = 'ID' --use primary key
--Store column names in a temp table
INSERT INTO @columnNames (colName, colIndex)
SELECT COL.name AS ColumnName, ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM sys.tables AS TAB
INNER JOIN sys.columns AS COL ON COL.object_id = TAB.object_id
WHERE TAB.name = @TableName
ORDER BY COL.column_id;
DECLARE @colName varchar(64)
SELECT @colName = colName FROM @columnNames WHERE colIndex = @colNumber
--Create Dynamic Query to retrieve the x,y coordinates from table
SET @sqlCommand = 'SELECT ' + @colName + ' FROM (SELECT ' + @colName + ', ROW_NUMBER() OVER (ORDER BY ' + @myColumnToOrderBy+ ') AS RowNum FROM ' + @tableName + ') t2 WHERE RowNum = ' + CAST(@rowNumber AS varchar(5))
EXEC(@sqlCommand)
