SQL Error 42703报错：percent_population_infected列不存在问题求助

报错原因 #

• 这是SQL执行顺序的典型问题，你在SELECT中给聚合结果定义的别名percent_population_infected，无法在WHERE子句中直接调用。SQL的执行优先级为：FROM→WHERE→GROUP BY→聚合计算→HAVING→SELECT→ORDER BY，WHERE执行时别名还未生成，所以数据库会提示列不存在。
• 聚合计算结果的过滤不能用WHERE子句，需要使用专门过滤聚合结果的HAVING子句。

修复后代码 #

方案1：直接使用HAVING子句（适配你当前的写法） #

/* 统计各国人口对应的最高感染率 */
SELECT LOCATION, population,
       MAX(total_cases) AS highest_infection_count,
       MAX((total_cases/population))*100 AS percent_population_infected
FROM public.Covid_deaths
GROUP BY LOCATION, population
HAVING MAX((total_cases/population))*100 IS NOT NULL 
ORDER BY percent_population_infected DESC;

方案2：使用CTE封装计算逻辑（可读性更高，无需重复写计算规则） #

WITH calc_result AS (
    SELECT LOCATION, population,
           MAX(total_cases) AS highest_infection_count,
           MAX((total_cases/population))*100 AS percent_population_infected
    FROM public.Covid_deaths
    GROUP BY LOCATION, population
)
SELECT * FROM calc_result
WHERE percent_population_infected IS NOT NULL
ORDER BY percent_population_infected DESC;

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