SQL同一条SELECT语句中如何在其他位置引用列别名？

报错原因说明 #

这是SQL执行逻辑的固有特性：同一条查询语句中，SELECT 子句中定义的别名，仅能在 ORDER BY 子句中直接引用，无法在同层的 SELECT 其他计算逻辑、WHERE/GROUP BY/HAVING 子句中使用——因为SQL的执行顺序是 FROM > WHERE > GROUP BY > 聚合计算 > SELECT 字段计算 > ORDER BY，别名是在SELECT字段计算阶段才生成，同层的CASE逻辑计算和别名生成属于同一阶段，所以无法提前识别。

以下3种方案都可以避开子查询用*的限制，且不会产生额外性能损耗： #

方案1：直接重复计算表达式（最简便） #

直接把温标转换的逻辑重复写到CASE判断中，大多数主流数据库的查询优化器会自动识别重复的表达式，只会执行一次计算，不会有性能损耗，也不需要嵌套子查询：

SELECT
  (temp_c * 9 / 5 + 32) AS temp_f,
  CASE
    WHEN (temp_c * 9 / 5 + 32) < @VALUE1 THEN "cold"
    WHEN (temp_c * 9 / 5 + 32) > @VALUE2 THEN "hot"
    ELSE "warm"
  END AS temp_description,
  other_columns
FROM some_table WHERE some_condition
ORDER BY temp_f;

方案2：使用CTE（公共表表达式，可读性更好） #

如果不想重复写计算逻辑，可以用CTE替代嵌套子查询，结构更清晰，你可以按需声明需要用到的列，不需要用*选择所有列：

WITH temp_calc AS (
  SELECT 
    (temp_c * 9 / 5 + 32) AS temp_f,
    other_columns -- 按需声明需要用到的列即可
  FROM some_table WHERE some_condition
)
SELECT
  temp_f,
  CASE
    WHEN temp_f < @VALUE1 THEN "cold"
    WHEN temp_f > @VALUE2 THEN "hot"
    ELSE "warm"
  END AS temp_description,
  other_columns
FROM temp_calc
ORDER BY temp_f;

方案3：使用横向派生表（支持的数据库可用，无需重复写逻辑也无需嵌套多层） #

如果你的数据库支持横向计算语法（PostgreSQL、MySQL 8.0+支持LATERAL，SQL Server支持CROSS APPLY），可以直接在FROM阶段提前计算好别名，后续所有子句都可以直接引用：

MySQL/PostgreSQL 写法：

SELECT
  tf.temp_f,
  CASE
    WHEN tf.temp_f < @VALUE1 THEN "cold"
    WHEN tf.temp_f > @VALUE2 THEN "hot"
    ELSE "warm"
  END AS temp_description,
  t.other_columns
FROM some_table t
JOIN LATERAL (SELECT (t.temp_c * 9 / 5 + 32) AS temp_f) tf ON TRUE
WHERE some_condition
ORDER BY tf.temp_f;

SQL Server 写法：

SELECT
  tf.temp_f,
  CASE
    WHEN tf.temp_f < @VALUE1 THEN "cold"
    WHEN tf.temp_f > @VALUE2 THEN "hot"
    ELSE "warm"
  END AS temp_description,
  t.other_columns
FROM some_table t
CROSS APPLY (SELECT (t.temp_c * 9 / 5 + 32) AS temp_f) tf
WHERE some_condition
ORDER BY tf.temp_f;

性能说明 #

以上三种方案的执行效率和原生嵌套子查询没有区别，主流数据库优化器都会对重复计算、CTE、横向派生表做优化，不会产生额外的计算开销，也不需要多次声明重复列。

内容的提问来源于stack exchange，提问作者Matt