问题描述
让我说在某张桌子上我有很多选择。列的一个值是使用复数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 [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）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！