问题描述
为什么这个 SQL 不起作用?
Why does this SQL not work?
:
6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935')
- RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) )
Clause 只是从搜索点计算顺序.
Clause just calculates the order from a search point.
我将其混叠(因为它太冗长)到距离.
Which I am aliasing (because it so longwinded) to Distance.
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
在这里,我用冗长的短语替换了距离
Here I replace the "Distance < 30" with the longwinded phrase and it works fine.
我什至可以按列别名 ORDER 并且有效!!?
I can even ORDER BY the column alias and that works!!?
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 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
我做错了什么?
推荐答案
出现这种情况是因为查询处理顺序很自然,如下:
This happens because of natural query processing order, which is the following:
FROM
开启
外层
WHERE
GROUP BY
CUBE
|ROLLUP
拥有
SELECT
DISTINCT
ORDER BY
TOP
您正在 SELECT
语句中指定别名.正如你所看到的,WHERE
在 SELECT
之前被处理,而 ORDER BY
在它之后.这就是原因.现在有什么解决方法:
You're assigning your alias in SELECT
statement. As you can see WHERE
is processed before SELECT
and ORDER BY
comes after it. That's the reason. Now what are the workarounds:
- 子查询.但它们可能难以阅读.
交叉申请
.这应该美化您的代码,并且是推荐的方法.
- Subqueries. But they can be hard to read.
CROSS APPLY
. This should beautify your code a bit and is recommended method.
CROSS APPLY
将在 WHERE
语句之前分配别名,使其在其中可用.
CROSS APPLY
will assign alias before WHERE
statement, making it usable in it.
SELECT [Hotel Id]
, latitude
, longitude
, establishmentname
, 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;
如果你想了解更多.请阅读这个问题:执行顺序是什么这个SQL语句
If you want to find out more. Please read this question: What is the order of execution for this SQL statement
这篇关于不能在 WHERE 子句中使用别名,但可以在 ORDER BY 中使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!