如何在同一查询的另一个字段中使用计算字段 [英] How to use calculated field in another field of the same query

问题描述

我有一个包含几十个字段的选择查询.FieldA"是一个大约 10 行高的 Case 语句.我现在必须制作一个FieldB"，它使用相同的 Case 语句加上一个常量.

I have a select query with a few dozen fields. "FieldA" is a Case statement about 10 rows tall. I now have to make a "FieldB" which uses the same Case statement plus a constant.

以我目前对 sql-server 的了解，我必须重复该 Case 语句两次(一次用于 FieldA，一次用于 FieldB).清理我的代码，如何在FieldB的计算中使用fieldA?

With my current knowledge of sql-server, I'd have to repeat that Case statement twice (once for FieldA and once for FieldB). To clean up my code, how can I use fieldA in the calculation of FieldB?

理想情况下，我的代码如下所示:

Ideally, my code would look something like this:

Select
    Case ...
        When ... then ...
        When ... then ...
        When ... then ...
    End                     as FieldA,
    FieldA + 1              as FieldB
From TblSource

(我知道一种选择是将数据转储到临时表中，然后更新该临时表.但这违背了简化"的概念)

(I know that one option is to dump the data into a temporary table, then update that temp table. But that kind of defeats the concept of 'simplifying')

推荐答案

这样做:

;WITH YourCTE AS
(
Select
    Case ...
        When ... then ...
        When ... then ...
        When ... then ...
    End                     as FieldA
From TblSource
)
SELECT FieldA, FieldA + 1 AS FieldB, FieldA + 2 AS FieldC ....
FROM YourCTE

这篇关于如何在同一查询的另一个字段中使用计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！