不能在 WHERE 子句中使用别名，但可以在 ORDER BY 中使用 [英] Cannot use Alias name in WHERE clause but can in ORDER BY

问题描述

为什么这个 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

在这里，我用冗长的短语替换了距离 <30"，效果很好.

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:

1. FROM
2. 开启
3. 外层
4. WHERE
5. GROUP BY
6. CUBE |ROLLUP
7. 拥有
8. SELECT
9. DISTINCT
10. ORDER BY
11. 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 中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！