将列的值用于另一个列(SQL Server)?

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 ColumnA and 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] + 10 is what im looking for. The complex stuff is a huge case/when block.

[ColumnA] + 10是我正在寻找的。复杂的东西是一个大箱子。

Ideas?

想法吗?

3 个解决方案

#1

If you want to reference a value that's computed in the SELECT clause, 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.

如果您已经完成了多个级别的部分计算，例如，如果您现在的计算依赖于在查询中包含ColumnB，那么cte将看起来更干净。

#2

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

#3

You could:

你可以:

Do the + 10 in the client code
在客户端代码中使用+ 10吗
Write a scalar-valued function to encapsulate the logic for complex stuff. It will be optimized into a single call.
编写一个标量值函数来封装复杂事物的逻辑。它将被优化为一个单独的调用。
Copy complex stuff logic for the other column. It should get optimized out into 1 call.
为另一列复制复杂的内容逻辑。它应该优化为一个调用。
Use a sub-select to apply the additional calculation
使用子select应用附加计算

#1