使用计算列的包含子句时出现查询错误

Question

我正在尝试使用数据库中的位置根据纬度和经度查找最近的位置。 这是我的查询：

select *,
acos(cos(33.7103820972222 * (PI()/180)) *
cos(73.05794 * (PI()/180)) *
cos(Lat * (PI()/180)) *
cos(Lon * (PI()/180))
+
cos(33.7103820972222 * (PI()/180)) *
sin(73.05794 * (PI()/180)) *
cos(Lat * (PI()/180)) *
sin(Lon * (PI()/180))
+
sin(33.7103820972222 * (PI()/180)) *
sin(73.05794 * (PI()/180))
) * 3959 AS Dist from tblOrg having Dist < 5 order by Dist

我在下面一行的 Dist 中遇到错误having Dist < 5.

Answer 1

正如marc_s所评论的那样"HAVING应该只与包含聚合函数(如SUM、COUNT等(的条件一起使用 - 否则，如果你的条件中没有任何聚合，请使用WHERE">

而且，不能直接在having或where子句中访问计算列。因此，请改为进行子查询：

select *
from (
select *,
acos(cos(33.7103820972222 * (PI()/180)) *
cos(73.05794 * (PI()/180)) *
cos(Lat * (PI()/180)) *
cos(Lon * (PI()/180))
+
cos(33.7103820972222 * (PI()/180)) *
sin(73.05794 * (PI()/180)) *
cos(Lat * (PI()/180)) *
sin(Lon * (PI()/180))
+
sin(33.7103820972222 * (PI()/180)) *
sin(73.05794 * (PI()/180))
) * 3959 AS Dist
from tblOrg
) x
where Dist < 5
order by Dist

Answer 2

这个查询终于解决了我的问题。

tblOrg.Id as Id,
( 6371  * acos( cos( radians(33.7103820972222) ) 
* cos( radians( Lat  ) ) 
* cos( radians( Lon  ) 
- radians(73.05794) ) 
+ sin( radians(33.7103820972222) ) 
* sin( radians( Lat ) ) ) ) AS Distance
FROM 
tblOrg 
) AS P
WHERE
P.Distance< 5   
ORDER BY
P.Distance;