SQL Server中Haversine公式的SQL查询 [英] SQL query of Haversine formula in SQL server

问题描述

我正在尝试从 SQL Server 2014 数据库中获取访问过特定区域的驱动程序.该表名为 DriverLocationHistory.

I am trying to get drivers who visited certain area from SQL server 2014 database. The table is named DriverLocationHistory.

这是我使用的 sql 查询:

Here is the sql query I used :

SELECT id, ( 6371 * acos( cos( radians(37) ) * cos( radians( latitude ) ) 
* cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(latitude)) ) ) AS distance 
FROM DriverLocationHistory 
HAVING distance < 5 
ORDER BY distance 

当我执行查询时出现此错误:

When I execute the query I get this error :

Msg 207, Level 16, State 1, Line 7
Invalid column name 'distance'.

推荐答案

不能在 Where 子句中使用别名.这是因为 where 子句是在生成结果集之前或在生成结果集时处理的，并且在生成结果集之前不会分配别名.只有在聚合查询(其中有一个 By 组)中才能这样做，因为在处理聚合之前，别名会被分配给某个表达式的值.您的查询必须在 where 子句(不必是 Hading 子句)和 order by 中使用完整表达式:

You can't use an alias in a Where clause. This is because the where clause is processed before, or as, the result set is being generated, and the alias is not assigned until the result set has been generated. Only in an aggregate query (where there is a group By) can you do this, because then the alias is assigned to the value of some expression before the aggregation is processed. Your query must use the full expression in both the where clause (does not need to be a Having clause) and in the order by:

SELECT id, 
  ( 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) ) ) AS distance 
FROM DriverLocationHistory 
Where 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) ) < 5 
ORDER BY 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) )

如评论中所述，您可以在 order by 中使用别名，

as mentioned in comments, you can use the alias in the order by,

或者，您也可以在子查询中执行计算和别名分配:

Or, you could also perform the computation and alias assignment in a subquery:

SELECT id, distance
From (Select ( 6371 * acos( cos( radians(37) )  
          * cos( radians( latitude ) ) 
          * cos( radians( Longitude ) - radians(-122) ) + 
             sin( radians(37) ) 
          * sin(radians(latitude)) ) ) distance 
      From DriverLocationHistory)z
Where distance < 5 
ORDER BY distance

这篇关于SQL Server中Haversine公式的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！