为什么这个sql不起作用？
的：
6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935')
- RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) )
子句只是从搜索点计算顺序.
哪个是混淆(因为它如此长久)到距离.
SELECT [Hotel Id],latitude,longitude,establishmentname,6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) AS Distance
FROM [dbo].[RPT_hotels]
WHERE distance < '30'
ORDER BY Distance
在这里,我用longwinded短语替换“Distance< 30”,它工作正常. 我甚至可以通过列别名对ORDER BY进行操作!!
SELECT [Hotel Id],6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) AS Distance
FROM [dbo].[RPT_hotels]
WHERE 6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) < '30'
ORDER BY Distance
我究竟做错了什么？
解决方法
这是因为自然查询处理顺序,如下所示：
>来自
>开
>外面
>在哪里
> GROUP BY
> CUBE |卷起
> HAVING
>选择
> DISTINCT
> ORDER BY
> TOP
您在SELECT语句中分配别名.正如您所看到的那样,在SELECT和ORDER BY之后处理WHERE.这就是原因.现在有哪些解决方法：
>子查询.但它们很难读懂.
>交叉申请.这应该美化你的代码一点,这是推荐的方法.
CROSS APPLY将在WHERE语句之前分配别名,使其可用.
SELECT [Hotel Id],Distance
FROM [dbo].[RPT_hotels]
CROSS APPLY (
SELECT 6371 * ACos(Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')))
) AS T(Distance)
WHERE distance < 30
ORDER BY Distance;
如果你想了解更多.请阅读这个问题：What is the order of execution for this SQL statement
