Re-use aliased field in SQL SELECT statement

问题:

I'd like to achieve something like this:

SELECT 
  (CASE WHEN ...) AS FieldA,
  FieldA + 20 AS FieldB
FROM Tbl

Assuming that by "..." I've replaced a long and complex CASE statement, I don't want to repeat it when selecting FieldB and use the aliased FieldA instead.

Note, that this will return multiple rows, hence the DECLARE/SET outside the SELECT statement is no good in my case.

回答1:

A workaroud would be to use a sub-query:

SELECT
  FieldA,
  FieldA + 20 AS FieldB
FROM (
  SELECT 
    (CASE WHEN ...) AS FieldA
  FROM Tbl
) t

---

To improve readability you could also use a CTE:

WITH t AS (
  SELECT 
    (CASE WHEN ...) AS FieldA
  FROM Tbl
)
SELECT
  FieldA,
  FieldA + 20 AS FieldB
FROM
  t

回答2:

When I have complicated logic to compute a "virtual" column value from other column values in a table I generally create a single-table view of the original table with all the original columns plus the computed values as well. Then I do other SELECTs against the view. That allows me:

1. To name my computed columns.

2. To keep the logic for the computations in one place instead of scattered through various queries in the application.