重用SQL SELECT语句中的别名字段 [英] Re-use aliased field in SQL SELECT statement

问题描述

我想实现以下目标：

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

假定...我替换了一个冗长而复杂的 CASE 语句，我不想在选择 FieldB 它$ c>并使用别名 FieldA 。

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.

请注意，这将返回多行，因此<在 SELECT 语句之外的code> DECLARE / SET 对我不利

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

推荐答案

一种工作方式是使用子查询：

A workaroud would be to use a sub-query:

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

---

要提高可读性，您还可以使用 CTE ：

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

这篇关于重用SQL SELECT语句中的别名字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！