sql-server-2005 - 如何在 SQL Server 的 where 子句中使用别名列名

Question

当我尝试在 SQL Server 2005 中执行以下代码时，出现错误

Invalid column name DistanceFromAddress

代码:

select 
    SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +   
    POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 AS DistanceFromAddress 
from 
    tblProgram 
where 
    DistanceFromAddress < 2

我使用 select 语句正确获取值，但是当我尝试检查条件 where DistanceFromAddress < 2 时我收到错误。

如何解决这个问题？

Answer 1

不能在 WHERE 子句中使用别名列。您可以尝试使用派生表。也许是这样的(抱歉，没有测试过):

SELECT * 
FROM (
    SELECT SQRT( POWER( cast(Program_Latitude as float) 
                        - cast('41.5126237' as float), 2) 
               + POWER( cast(Program_Longitude as float) 
                        - cast('-81.6516411' as float), 2)
               ) * 62.1371192    AS DistanceFromAddress 
    from tblProgram 
    ) mytable
WHERE DistanceFromAddress < 2