如何在whereand子句中使用sql选择数据？

我有这段mysql代码，工作正常，但我想知道如何才能避免做两次RAW数据计算，这是我的代码。

SELECT `users`.*, ( 3959 * ACOS(COS(RADIANS(43.9743)) *
  COS(RADIANS(locations.lat)) * 
  COS( RADIANS(-75.9122) - RADIANS(locations.LONG)) +
                              SIN(RADIANS(43.9743)) *
  SIN(RADIANS(locations.lat) )) ) AS distance 
FROM   `users` 
INNER JOIN `addresses` ON `users`.`id` = `addresses`.`user_id` 
LEFT JOIN `locations` ON `addresses`.`zip` = `locations`.`zip` 
WHERE  `type` = 'doctor' 
  AND ( 3959 * ACOS(COS(RADIANS(43.9743)) * COS(RADIANS(locations.lat)) *
    COS( RADIANS(-75.9122) - RADIANS(locations.LONG)) +
    SIN(RADIANS(43.9743)) * SIN(RADIANS(locations.lat) )) ) < 100
  AND `users`.`deleted_at` IS NULL 
ORDER BY distance ASC
LIMIT  15 OFFSET 0 

如你所见，我用了两次这样的计算方法。( 3959 * ACOS(COS(RADIANS(43.9743)) * COS(RADIANS(locations.lat)) * COS( RADIANS(-75.9122) - RADIANS(locations.LONG)) + SIN(RADIANS(43.9743)) * SIN(RADIANS(locations.lat) )) ) 关于 AND 和 选择 声明。

我试过让 AND distance < 100 但我得到了一个错误，说列不存在。

感谢任何帮助

解决方案：

MySQL扩展使用了 having 子句，这是解决这个问题的最好方法。

SELECT u.*,
       ( 3959 * ACOS(COS(RADIANS(43.9743)) * COS(RADIANS(l.lat) ) * 
                     COS( RADIANS(-75.9122) - RADIANS(l.LONG)) +
                     SIN(RADIANS(43.9743)) * SIN(RADIANS(l.lat))
                    )
       ) AS distance 
FROM `users` u INNER JOIN
     `addresses` a 
     ON u.`id` = a.`user_id` LEFT JOIN
     `locations` l
     ON a.`zip` = l.`zip` 
WHERE `type` = 'doctor' AND u.`deleted_at` IS NULL 
HAVING distance < 100
ORDER BY distance ASC
LIMIT  15 OFFSET 0 

这样做比较好是因为MySQL有将子查询和CTE具体化的习惯，这会增加额外的开销。