在SQL Server中拥有无效的列名称

I have quick question why I can't use having keyword on distance? I need somehow to check is distance < 20 for example

我很快就会问为什么我不能在距离上使用关键字？我需要以某种方式检查距离<20例如

SELECT 
    Id, Lat, Lng,
    (6367 * acos( cos( radians(45.444) )
      * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(158.554) )
      + sin( radians(4545) ) * sin( radians( Lat ) ) ) ) AS distance 
FROM 
    Posts
HAVING 
    distance < 15  // Invalid column Name 
ORDER BY 
    distance

3 个解决方案

#1

Try this

尝试这个

SELECT * 
FROM
    (SELECT 
         Id, Lat, Lng,
         (6367 * acos(cos(radians(45.444)) * cos(radians(Lat)) * 
          cos(radians(Lng) - radians(158.554)) + sin(radians(4545)) * 
          sin(radians(Lat)))) AS distance 
     FROM Posts) p
WHERE 
    p.distance < 15  
ORDER BY 
    p.distance

#2

I might suggest outer apply for this purpose:

我可能建议外部申请这个目的：

SELECT p.Id, p.Lat, p.Lng, d.distance
FROM Posts p OUTER APPLY
     (SELECT (6367 * acos( cos( radians(45.444) )
             * cos( radians( p.Lat ) ) * cos( radians( p.Lng ) - radians(158.554) )
             + sin( radians(4545) ) * sin( radians( p.Lat ) ) ) ) AS distance 
     ) d
FROM Posts p
WHERE d.distance < 15 
ORDER BY distance;

The use of HAVING as a substitute for WHERE is an extension for MySQL. In other databases, you can use a subquery, CTE, or lateral join (which is the technical name for what APPLY does). In this case, I think the lateral join is convenient, because it separates the logic for this quite complicated formula.

使用HAVING作为WHERE的替代是MySQL的扩展。在其他数据库中，您可以使用子查询，CTE或横向连接（这是APPLY所做的技术名称）。在这种情况下，我认为横向连接很方便，因为它分离了这个相当复杂的公式的逻辑。

#3

-2

YOu can use brackets to dictate you mean a field name not a pereserved keyword:

你可以使用括号来表示你是指一个字段名称而不是一个pereserved关键字：

SELECT Id,Lat,Lng,(6367 * acos( cos( radians(45.444) )
      * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(158.554) )
      + sin( radians(4545) ) * sin( radians( Lat ) ) ) ) AS distance FROM Posts
       HAVING [distance] < 15  // Invalid column Name 
       ORDER BY distance

#1