如何在SQL服务器中的别名列中设置条件?如何在距离列中设置小于条件?

Question

SELECT  
latitude, longitude, 
SQRT(POWER(69.1 * (latitude - 29.0998573), 2) +
POWER(69.1 * (76.3466209 - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM 
driverAvailable

Answer 1

您可以在WHERE子句中重复distance的公式：

SELECT  
latitude, longitude, 
SQRT(POWER(69.1 * (latitude - 29.0998573), 2) +
POWER(69.1 * (76.3466209 - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM 
driverAvailable
WHERE 
SQRT(POWER(69.1 * (latitude - 29.0998573), 2) +
POWER(69.1 * (76.3466209 - longitude) * COS(latitude / 57.3), 2)) < 25

或者您可以使用 CTE(公用表表达式(，然后使用定义的别名列：

WITH DistanceData AS
( 
SELECT  
latitude, longitude, 
SQRT(POWER(69.1 * (latitude - 29.0998573), 2) +
POWER(69.1 * (76.3466209 - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM 
driverAvailable
)
SELECT 
latitude, longitude, distance
FROM
DistanceData
WHERE
distance < 25

您只是不能在WHERE子句的SELECT查询中直接使用列别名 - 您必须使用 CTE 之类的东西才能执行此操作