mysql - 我需要一张临时 table 吗？

我有以下查询，需要在where子句中添加“and distance我想答案是使用一个临时表来计算距离，但是我无法找出语法，因为我尝试过的所有方法都不起作用。
谢谢大家的帮助。谢谢。

select
    Contractor.contractorID,
    Contractor.firstName,
    Contractor.lastName,
    Contractor.emailAddress,
    Contractor.nationality,
    Contractor.dateOfBirth,
    Contractor.address1,
    Contractor.address2,
    Contractor.city,
    Contractor.county,
    Contractor.postcode,
    Contractor.country,
    Contractor.tel,
    Contractor.mob,
    postcodes.Grid_N Grid_N1,
    postcodes.Grid_E Grid_E1,
    (select Grid_N from postcodes where pCode='".$postcode."') Grid_N2,
    (select Grid_E from postcodes where pCode='".$postcode."') Grid_E2,
    ( (select sqrt(((Grid_N1-Grid_N2)*(Grid_N1-Grid_N2))+((Grid_E1-Grid_E2)*(Grid_E1-Grid_E2))) ))/1000*0.621371192 as distance
from
    Contractor,
    postcodes
where
    postcodes.Pcode = replace(substring(Contractor.postcode,1,length(Contractor.postcode)-3),'','')
order by
    distance asc

最佳答案

在MySQL中，您可以使用：

where . . .
having distance < 10
order by distance;

您不需要在having子句中添加其他条件。此外，使用ANSI标准连接语法（例如使用on子句）可以使查询受益。