PostgreSQL查询别名distance报错及空结果集问题求助

问题1：别名distance报错不存在 #

执行以下SQL时，触发错误ERROR: column "distance" does not exist：

SELECT rentalid, createdDate, votecount AS distance
FROM rental 
WHERE longitude=? AND latitude=?   
HAVING distance < 25 ORDER BY distance LIMIT 0 OFFSET 30

核心原因 #

1. SQL执行顺序限制：SQL执行流程为FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY。HAVING在SELECT之前执行，此时SELECT中定义的别名distance还未生成，自然无法识别。
2. 误用HAVING子句：你的查询没有分组逻辑（无GROUP BY），完全不需要使用HAVING，应该用WHERE筛选。但注意WHERE同样不能直接引用SELECT的别名，要么重复字段逻辑，要么改用子查询。

修正后的SQL（仅解决别名报错） #

SELECT rentalid, createdDate, votecount AS distance
FROM rental 
WHERE longitude=? AND latitude=?   
  AND votecount < 25 
ORDER BY distance LIMIT 30 OFFSET 0

（注：按你要求忽略votecount作为distance的业务逻辑，此处仅针对语法报错修正）

问题2：调整SQL后返回空结果集 #

你修改后的代码中，SQL存在两个致命错误导致无数据返回：

public List<RentalDto> selectRentalsByDistance(Double lon, Double lat) {
        var sql = """
                SELECT *
                FROM (
                    SELECT rentalid, createdDate, 
                    ( 3959 * acos( cos(radians(37) ) * cos( radians(latitude) ) * cos( radians(longitude) - radians(-122) )
                                    + sin( radians(37) ) * sin( radians(latitude) )) ) as distance
                    FROM rental
                    WHERE lon = ? AND lat = ?                                    
                ) AS subquery
                WHERE distance < 25
                ORDER BY distance
                LIMIT 30 OFFSET 0
                """;
        return jdbcTemplate.query(sql, rentalDtoRowMapper, new Object[] {lon, lat});
    }

错误点解析 #

1. WHERE子句字段匹配错误：表中字段是longitude和latitude，但SQL里写的是lon = ? AND lat = ?，既匹配了不存在的字段，更关键的是逻辑错误——你需要计算所有记录到传入坐标的距离，而非筛选表中经纬度完全等于参数的记录。
2. 距离计算参数硬编码：公式里的radians(37)和radians(-122)是固定值，没有替换为传入的lat和lon，导致计算的是到固定坐标的距离，而非目标坐标。

修正后的代码 #

public List<RentalDto> selectRentalsByDistance(Double lon, Double lat) {
        var sql = """
                SELECT *
                FROM (
                    SELECT rentalid, createdDate, 
                    ( 3959 * acos( cos(radians(?)) * cos(radians(latitude)) * cos(radians(longitude) - radians(?))
                                    + sin(radians(?)) * sin(radians(latitude)) )) as distance
                    FROM rental
                    -- 可选：数据量大时添加粗略范围过滤，减少计算量
                    -- WHERE longitude BETWEEN ? - 0.5 AND ? + 0.5 
                    --   AND latitude BETWEEN ? - 0.5 AND ? + 0.5
                ) AS subquery
                WHERE distance < 25
                ORDER BY distance
                LIMIT 30 OFFSET 0
                """;
        return jdbcTemplate.query(sql, rentalDtoRowMapper, new Object[] {lat, lon, lat});
    }

关键修正说明 #

• 将硬编码的坐标参数替换为占位符，对应传入的lat、lon、lat（公式中纬度参数需要使用两次）。
• 移除错误的WHERE lon = ? AND lat = ?子句，确保计算所有记录到目标坐标的距离；若数据量庞大，可添加注释中的粗略经纬度范围过滤，提升查询性能。
• 外层WHERE可直接使用子查询的distance别名，因为子查询的SELECT执行顺序早于外层WHERE。

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