将一列的值用于另一列（SQL Server）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5180979/
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
Use value of a column for another column (SQL Server)?
提问by grady
lets say I have a huge select on a certain table. One value for a column is calculated with complex logc and its called ColumnA. Now, for another column, I need the value from ColumnAand add some other static value to it.
假设我在某张桌子上有很多选择。一列的一个值是用复杂的 logc 计算的，它被称为ColumnA。现在，对于另一列，我需要来自ColumnA 的值并向其添加一些其他静态值。
Sample SQL:
示例 SQL：
select table.id, table.number, complex stuff [ColumnA], [ColumnA] + 10 .. from table ...
The [ColumnA] + 10is what im looking for. The complex stuff is a huge case/when block.
在[ColumnA] + 10是什么即时寻找。复杂的东西是一个巨大的案例/当块。
Ideas?
想法？
回答by Damien_The_Unbeliever
If you want to reference a value that's computed in the SELECTclause, you need to move the existing query into a sub-SELECT:
如果要引用在SELECT子句中计算的值，则需要将现有查询移动到子 SELECT 中：
SELECT
/* Other columns */,
ColumnA,
ColumnA + 10 as ColumnB
FROM
(select table.id, table.number, complex stuff [ColumnA].. from table ...
) t
You have to introduce an alias for this table (in the above, t, after the closing bracket) even if you're not going to use it.
t即使您不打算使用它，您也必须为此表引入一个别名（在上面的 , 在右括号之后）。
(Equivalently - assuming you're using SQL Server 2005 or later - you can move your existing query into a CTE):
（等效地 - 假设您使用的是 SQL Server 2005 或更高版本 - 您可以将现有查询移动到 CTE）：
;WITH PartialResults as (
select table.id, table.number, complex stuff [ColumnA].. from table ...
)
SELECT /* other columns */, ColumnA, ColumnA+10 as ColumnB from PartialResults
CTEs tend to look cleaner if you've got multiple levels of partial computations being done, I.e. if you've now got a calculation that depends on ColumnB to include in your query.
如果您已经完成了多个级别的部分计算，则 CTE 往往看起来更清晰，即，如果您现在有一个依赖于 ColumnB 的计算以包含在您的查询中。
回答by Ludovic Aubert
Unfortunately, in SQL Server 2016:
不幸的是，在 SQL Server 2016 中：
SELECT 3 AS a, 6/a AS b;
Error: Invalid column name: 'a'.
错误：无效的列名：'a'。
回答by Enrico Campidoglio
You could solve this with a subquery and column aliases.
您可以使用子查询和列别名来解决此问题。
Here's an example:
下面是一个例子：
SELECT MaxId + 10
FROM (SELECT Max(t.Id) As MaxId
FROM SomeTable t) As SomeTableMaxId
回答by tenfour
You could:
你可以：
- Do the
+ 10in the client code - Write a scalar-valued function to encapsulate the logic for
complex stuff. It will be optimized into a single call. - Copy
complex stufflogic for the other column. It should get optimized out into 1 call. - Use a sub-select to apply the additional calculation
- 做
+ 10在客户端代码 - 编写一个标量值函数来封装 的逻辑
complex stuff。它将被优化为单个调用。 - 复制
complex stuff另一列的逻辑。它应该优化为 1 个调用。 - 使用子选择来应用额外的计算
