将一列的值用于另一列（SQL Server）？ [英] Use value of a column for another column (SQL Server)?

问题描述

让我说在某张桌子上我有很多选择。列的一个值是使用复数logc及其称为 ColumnA 的值来计算的。现在，对于另一列，我需要 ColumnA 中的值，并向其中添加其他一些静态值。

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.

示例SQL：

select table.id, table.number, complex stuff [ColumnA], [ColumnA] + 10 .. from table ...

The <我正在寻找em> [ColumnA] + 10 。

想法？

推荐答案

如果要引用在 SELECT 子句中计算的值，则需要将现有查询移至子SELECT：

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
    /* Other columns */,
    ColumnA,
    ColumnA + 10 as ColumnB
FROM
(select table.id, table.number, complex stuff [ColumnA].. from table ...
) t

即使您不是这样，也必须为此表引入一个别名（在上面， 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.

（等效地-假设您使用的是SQL Server 2005或更高版本-您可以将现有查询移至CTE中）：

(Equivalently - assuming you're using SQL Server 2005 or later - you can move your existing query into a CTE):

;WITH PartialResults as (
     select table.id, table.number, complex stuff [ColumnA].. from table ...
)
SELECT /* other columns */, ColumnA, ColumnA+10 as ColumnB from PartialResults

如果您已完成多个级别的部分计算，则CTE看上去会更干净。如果您现在有了一个取决于ColumnB的计算来包含在查询中。

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.

这篇关于将一列的值用于另一列（SQL Server）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！