mysql - 如何指定在此 MySQL 查询中包含特定的邮政编码？

Question

此查询中使用的基本邮政编码是 90210 的纬度/经度，其平均 agi(2008 年)超过 40 万美元。我不希望它出现在这个输出中，因为 BETWEEN 范围为 100000 到 200000。

SELECT 
  zip, city, state, avg_agi, 
  ( 3959 
      * acos( 
              cos( radians(34.088808) ) 
            * cos( radians( lat ) ) 
            * cos( radians( lng ) - radians(-118.40612) ) 
            + sin( radians(34.088808) ) 
            * sin( radians( lat ) ) 
        ) 
  ) AS distance 
FROM 
  zip_codes, avgagi 
where 
      zip=zipcode 
  and avg_agi BETWEEN 100000 and 200000 
HAVING 
  distance < 5 ORDER BY distance LIMIT 0 , 10000;

+-------+----------------+-------+---------+------------------+
| zip   | city           | state | avg_agi | distance         |
+-------+----------------+-------+---------+------------------+
| 90069 | West Hollywood | CA    |  121753 | 1.42816585190112 |
| 90211 | Beverly Hills  | CA    |  164538 | 2.06804933097035 |
| 90024 | Los Angeles    | CA    |  187134 | 2.47751318825072 |
| 90025 | Los Angeles    | CA    |  130983 | 3.76591348160737 |
| 91604 | Studio City    | CA    |  103328 |  3.8634176735557 |
| 90064 | Los Angeles    | CA    |  130769 | 3.95933331921038 |
| 90068 | Los Angeles    | CA    |  100370 | 4.52908379278674 |
+-------+----------------+-------+---------+------------------+

但是，对于我的应用程序，我需要它在输出中包含基本邮政编码。有人建议我使用 WHERE 和 OR 来表示 zip="90210"，但是我很难弄清楚如何实现这一点。当我尝试采纳该建议时，它旋转了最长的时间并且再也没有回来。

如何修改上述 MySQL 查询以使其包含邮政编码 90210，而不管 BETWEEN 范围如何。谢谢!

Answer 1

使用 UNION 语句可能更容易:

SELECT 
  zip, city, state, avg_agi, 0 AS distance 
FROM 
  zip_codes, avgagi 
where 
      zip=zipcode 
UNION
SELECT 
  zip, city, state, avg_agi, 
  ( 3959 
      * acos( 
              cos( radians(34.088808) ) 
            * cos( radians( lat ) ) 
            * cos( radians( lng ) - radians(-118.40612) ) 
            + sin( radians(34.088808) ) 
            * sin( radians( lat ) ) 
        ) 
  ) AS distance 
FROM 
  zip_codes, avgagi 
where 
      zip=zipcode 
  and avg_agi BETWEEN 100000 and 200000 
HAVING 
  distance < 5 ORDER BY distance LIMIT 0 , 10000;

否则试试这个:

SELECT 
      zip, city, state, avg_agi, 
      ( 3959 
          * acos( 
                  cos( radians(34.088808) ) 
                * cos( radians( lat ) ) 
                * cos( radians( lng ) - radians(-118.40612) ) 
                + sin( radians(34.088808) ) 
                * sin( radians( lat ) ) 
            ) 
      ) AS distance 
    FROM 
      zip_codes, avgagi 
    where 
          zip=zipcode 
    AND(zip = 90210
    OR avg_agi BETWEEN 100000 and 200000 )
    HAVING 
      distance < 5 ORDER BY distance LIMIT 0 , 10000;