使用HAVING子句执行SQL时报错：无效的列名distance

SQL语句的逻辑执行顺序和书写顺序不一致，你在SELECT子句中定义的别名distance，其生效时机晚于HAVING子句的执行时机，因此HAVING子句无法识别该别名。
另外HAVING子句的设计用途是对GROUP BY分组后的聚合结果进行过滤，你当前的查询没有分组逻辑，本身也不应该使用HAVING做过滤。

以下是三种常用的可行方案：

方案1：直接在WHERE中写入计算逻辑（最简单高效） #

不需要用别名，直接把距离计算逻辑放到WHERE子句中，替换原有的HAVING即可：

SELECT 
    LocationId, 
    (3959 *
     acos(cos(radians(37)) * 
     cos(radians(Latitude)) * 
     cos(radians(Longitude) - 
     radians(-122)) + 
     sin(radians(37)) * 
     sin(radians(Latitude)))) AS distance 
FROM 
    [dbo].[UserLocation]
WHERE 
    (3959 *
     acos(cos(radians(37)) * 
     cos(radians(Latitude)) * 
     cos(radians(Longitude) - 
     radians(-122)) + 
     sin(radians(37)) * 
     sin(radians(Latitude)))) < 28 
ORDER BY 
    distance

方案2：用CTE封装计算逻辑（可读性更好） #

先通过公用表表达式预先计算出所有位置的距离，再在外层做过滤：

WITH LocationDistance AS (
    SELECT 
        LocationId, 
        (3959 *
         acos(cos(radians(37)) * 
         cos(radians(Latitude)) * 
         cos(radians(Longitude) - 
         radians(-122)) + 
         sin(radians(37)) * 
         sin(radians(Latitude)))) AS distance 
    FROM 
        [dbo].[UserLocation]
)
SELECT LocationId, distance
FROM LocationDistance
WHERE distance < 28
ORDER BY distance

方案3：用子查询嵌套（兼容更低版本的SQL Server） #

逻辑和CTE类似，嵌套一层子查询完成距离计算后再过滤：

SELECT LocationId, distance
FROM (
    SELECT 
        LocationId, 
        (3959 *
         acos(cos(radians(37)) * 
         cos(radians(Latitude)) * 
         cos(radians(Longitude) - 
         radians(-122)) + 
         sin(radians(37)) * 
         sin(radians(Latitude)))) AS distance 
    FROM 
        [dbo].[UserLocation]
) AS T
WHERE T.distance < 28
ORDER BY T.distance

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