PostgreSQL报错：relation "temp"不存在，求问题原因与解析

报错含义 #

这条错误明确表示：PostgreSQL无法识别名为temp的关系（这里的关系指表、视图或派生表）。你在WHERE子句的子查询里试图从temp获取最大值，但数据库找不到这个temp对象。

错误原因 #

这是PostgreSQL的作用域规则导致的问题：你在FROM子句里定义的派生表别名temp，属于外层查询的作用域范围；而WHERE子句中嵌套的子查询是一个独立的内层查询，它无法访问外层查询中定义的派生表别名。简单来说，内层子查询“看不到”外层临时定义的temp结果集。

修正方案 #

有两种高效的解决方式：

方式1：使用CTE（公共表表达式） #

将派生表改写为CTE，这样整个查询范围内都能引用这个临时结果集：

WITH temp AS (
    select bike_id, start_time, start_station_id, a.name as start_station, a.latitude as start_lat, a.longitude as start_lon,
    end_time, end_station_id, b.name as end_station, b.latitude as end_lat, b.longitude as end_lon,
    calculate_distance(a.latitude, a.longitude,  b.latitude, b.longitude,  'K') as journey_distance
    from baywheels_2019 
    left join baywheels_stations a on start_station_id = a.id
    left join baywheels_stations b on end_station_id = b.id
)
select bike_id, start_time, start_station, start_station_id, end_time, end_station, end_station_id, journey_distance    
from temp
where journey_distance = (select max(journey_distance) from temp);

方式2：使用窗口函数（性能更优） #

用RANK()窗口函数直接标记出最大距离的记录，避免二次查询：

SELECT bike_id, start_time, start_station, start_station_id, end_time, end_station, end_station_id, journey_distance
FROM (
    select bike_id, start_time, start_station_id, a.name as start_station, a.latitude as start_lat, a.longitude as start_lon,
    end_time, end_station_id, b.name as end_station, b.latitude as end_lat, b.longitude as end_lon,
    calculate_distance(a.latitude, a.longitude,  b.latitude, b.longitude,  'K') as journey_distance,
    RANK() OVER(ORDER BY calculate_distance(a.latitude, a.longitude,  b.latitude, b.longitude,  'K') DESC) as rnk
    from baywheels_2019 
    left join baywheels_stations a on start_station_id = a.id
    left join baywheels_stations b on end_station_id = b.id
) temp
WHERE rnk = 1;

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