sql – 不能在WHERE子句中使用别名,但可以在ORDER BY中使用

的：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!