mysql - 在 HAVING 子句中添加查询条件时遇到问题

Question

我的数据库中有一条位于 16 英里处的 Blocks Realty 记录。

SELECT x.companyname, x.distance, y.city, y.zip 
FROM (SELECT userid, companyname, lat, lng, 
(3959 * acos(cos(radians(42.323617)) * 
cos(radians(lat)) * cos(radians(lng) - radians(-83.200811)) + 
sin(radians(42.323617)) * sin(radians(lat)))) AS distance
FROM users 
WHERE (usertype = 2 OR usertype = 3) 
HAVING distance < 25
order by distance) x 
INNER JOIN 
(select * from users where find_in_set('Real Estate',profession) <> 0) y 
ON x.userid = y.userid

当我更新下面的查询以添加城市或邮政编码条件并减小距离搜索范围时，我收到错误消息 #1054 - Unknown column 'city' in 'having clause'。我该如何纠正这个问题？

SELECT x.companyname, x.distance, y.city, y.zip 
FROM (SELECT userid, companyname, lat, lng, 
(3959 * acos(...) AS distance
FROM users 
WHERE (usertype = 2 OR usertype = 3) 
HAVING distance < 11 
AND (city = 'Detroit' OR zip = '48221') 
order by distance) x 
INNER JOIN 
(select * from users where find_in_set('Real Estate',profession) <> 0) y 
ON x.userid = y.userid

Answer 1

将这些条件移动到 where 子句:

SELECT x.companyname, x.distance, y.city, y.zip 
FROM (SELECT userid, companyname, lat, lng, 
             (3959 * acos(...) AS distance
      FROM users 
      WHERE usertype IN (2, 3) AND
            (city = 'Detroit' OR zip = '48221') 
      HAVING distance < 11 
      order by distance
    ) x INNER JOIN 
    (select * from users where find_in_set('Real Estate',profession) <> 0) y 
    ON x.userid = y.userid;

编辑:

您的原始版本不起作用的原因是 having 子句需要引用 select 中定义的列别名。所以，你也可以这样做:

SELECT x.companyname, x.distance, y.city, y.zip 
FROM (SELECT userid, companyname, lat, lng, city, zip
             (3959 * acos(...) AS distance
      FROM users 
      WHERE usertype IN (2, 3)                 
      HAVING distance < 11 OR (city = 'Detroit' OR zip = '48221')
      order by distance
    ) x INNER JOIN 
    (select * from users where find_in_set('Real Estate',profession) <> 0) y 
    ON x.userid = y.userid;