如何在SELECT的计算列中引用同查询内定义的计算列别名somaDiarias

报错原因 #

SQL的执行顺序决定了同层级SELECT子句中定义的别名不能被同层级的其他表达式直接引用：数据库执行时会先处理FROM/JOIN、WHERE等子句，最后才处理SELECT里的字段计算和别名赋值，所以你在CASE里直接用somaDiarias的时候，这个别名还没被识别，就会报不存在的错误。

解决方案 #

以下方案都只需要写一次somaDiarias的计算逻辑，符合你的需求：

方案1：用子查询封装计算逻辑 #

把somaDiarias的计算放到内层子查询，外层查询直接引用别名做判断：

SELECT 
  nomeHospede,
  somaDiarias,
  CASE WHEN (somaDiarias >=0) AND (somaDiarias <= 1000) THEN 'E'
       WHEN (somaDiarias >=1000.01) AND (somaDiarias <= 3000.00) THEN 'D'
       WHEN (somaDiarias >=3000.01) AND (somaDiarias <= 7000.00) THEN 'C'
       WHEN (somaDiarias >=7001.00) AND (somaDiarias <= 10000) THEN 'b'
       WHEN (somaDiarias >10000) THEN '10'
       END AS Classe
FROM (
  SELECT 
    nome AS nomeHospede,
    valorDiaria*DATE_PART('day',dataSaida::timestamp - dataEntrada::timestamp) AS somaDiarias
  FROM hospedagem a
  JOIN hospede b on a.codHospede = b.codHospede
) t

方案2：用CTE（公共表表达式） #

逻辑和子查询类似，可读性更高，适合复杂多层计算的场景：

WITH hospedagem_calc AS (
  SELECT 
    nome AS nomeHospede,
    valorDiaria*DATE_PART('day',dataSaida::timestamp - dataEntrada::timestamp) AS somaDiarias
  FROM hospedagem a
  JOIN hospede b on a.codHospede = b.codHospede
)
SELECT 
  nomeHospede,
  somaDiarias,
  CASE WHEN (somaDiarias >=0) AND (somaDiarias <= 1000) THEN 'E'
       WHEN (somaDiarias >=1000.01) AND (somaDiarias <= 3000.00) THEN 'D'
       WHEN (somaDiarias >=3000.01) AND (somaDiarias <= 7000.00) THEN 'C'
       WHEN (somaDiarias >=7001.00) AND (somaDiarias <= 10000) THEN 'b'
       WHEN (somaDiarias >10000) THEN '10'
       END AS Classe
FROM hospedagem_calc

方案3：用LATERAL JOIN（PostgreSQL专属） #

如果用的是PostgreSQL，可以用LATERAL JOIN提前计算字段，不需要嵌套多层：

SELECT 
  nome AS nomeHospede,
  s.somaDiarias,
  CASE WHEN (s.somaDiarias >=0) AND (s.somaDiarias <= 1000) THEN 'E'
       WHEN (s.somaDiarias >=1000.01) AND (s.somaDiarias <= 3000.00) THEN 'D'
       WHEN (s.somaDiarias >=3000.01) AND (s.somaDiarias <= 7000.00) THEN 'C'
       WHEN (s.somaDiarias >=7001.00) AND (s.somaDiarias <= 10000) THEN 'b'
       WHEN (s.somaDiarias >10000) THEN '10'
       END AS Classe
FROM hospedagem a
JOIN hospede b on a.codHospede = b.codHospede
LATERAL (
  SELECT valorDiaria*DATE_PART('day',dataSaida::timestamp - dataEntrada::timestamp) AS somaDiarias
) s

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