mysql - 在另一个用户邮政编码的半径范围内定位用户

Question

我有一个站点，允许用户在他们自己的邮政编码的指定半径范围内搜索其他用户。我首先必须定位用户的纬度和经度，并使用该数据计算并找到其他用户。我已经根据 2 个必需的表准备了以下查询，但收到需要另一个别名的错误。如果我对这些值进行硬编码，那么当我将它们拆分出来时，部分查询工作会找到，但是对于多个连接和子查询，我一直没有成功。寻找任何指导或帮助。下面包含查询和表结构。

根据反馈编辑查询和数据集。不再收到错误，但没有收到任何返回的查询数据。

SQL语句

SELECT u.username, u.zip, m.longitude,
  m.latitude,
  m.zip from users u, zipcodes m  
LEFT JOIN (SELECT  s.zip,
  s.username from users s) t
 on m.zip =  t.zip
  and t.username = 'tester55'
  INNER join ( SELECT zip, city, state, ( 3959 * acos( cos( radians(@m.latitude) ) * cos( radians( latitude ) ) 
    * cos( radians( longitude ) - radians(@m.longitude) ) + sin( radians(@m.latitude) ) * sin(radians(latitude)) ) ) AS distance 
FROM zipcodes
HAVING distance < 5000
ORDER BY distance) as RelevantCodes
  ON (t.zip=RelevantCodes.zip)

用户表

    username     zip
-----------------------
    tester55     60608
    Tester1      60608
    iosuser1     73120
    Tester3      60608

zip 表

latitude     longitude     zip
--------------------------------
41.8515      -87.6694      60608
35.5835      -97.5638      73120
36.3169      -94.1545      72758

Answer 1

我现在没有时间计算出正确的查询，但我会第一眼展示现有查询的错误之处。

1) 关于您的实际问题:错误消息显示为 ERROR 1248 (42000): Every derived table must have its own alias并且已经说明了问题所在。下面部分的子查询

LEFT JOIN (select s.zip,
  s.username,
  m.longitude,
  m.latitude,
  m.zip
from users s) on u.zip =  s.zip

需要自己的别名，所以最终应该是

LEFT JOIN (select s.zip,
  s.username,
  m.longitude,
  m.latitude,
  m.zip
from users s) t on u.zip =  t.zip

(注意右括号后的 t 以及用 t.zip 替换 s.zip)。但即使这样做，您也会遇到问题 2(请参阅下一节)。

2) 子查询

(select s.zip,
        s.username,
        m.longitude,
        m.latitude,
        m.zip
        from users s)

将抛出另一个错误，因为表 m 在该子查询中是未知的。

如果您能够正确解决问题，我建议您编辑您的问题并让我们知道结果。如果没有，请发表评论或编辑您的问题，描述您遇到的其他问题。

编辑

我现在已经为您制定了解决方案:

SELECT zipcodes.zip,
       (3959 * acos(cos(radians(zipcodes.latitude)) *
                    cos(radians(center.latitude)) *
                    cos(radians(zipcodes.longitude ) -
                        radians(center.longitude)) +
                    sin(radians(zipcodes.latitude)) *
                    sin(radians(center.latitude)))) AS distance FROM
  ( SELECT users.username,
           users.zip,
           zipcodes.latitude,
           zipcodes.longitude FROM
    (users JOIN zipcodes ON users.zip = zipcodes.zip)
    WHERE (username='Tester1')
  ) center, zipcodes
HAVING (distance < 5000)
ORDER BY distance;

这会选择所需距离内的所有邮政编码，并按距离排序。

您可能会注意到，我已经简化了代码。另请注意，我已经根据您的旧数据(您在编辑前显示的数据)对此进行了测试，但使用 Tester1而不是 tester55 .

您确实需要 HAVING而不是 WHERE过滤时，所以我最初的声明具有误导性。原因是 MySQL 不允许在 WHERE 中计算列(在本例中为距离)。条款，但在HAVING条款。这是非标准行为；通常，你必须用另一种方式解决这个问题。我不会对此进行详细说明，因为它是另一个可能令人担忧的事情，与您的原始问题没有密切关系。

编辑 2

最后，这是另外提供用户名的版本:

SELECT zipcodes.zip,
       users.username,
       (3959 * acos(cos(radians(zipcodes.latitude)) *
                    cos(radians(center.latitude)) *
                    cos(radians(zipcodes.longitude ) -
                        radians(center.longitude)) +
                    sin(radians(zipcodes.latitude)) *
                    sin(radians(center.latitude)))) AS distance FROM
  ( ( SELECT users.username,
             users.zip,
             zipcodes.latitude,
             zipcodes.longitude FROM
      (users JOIN zipcodes ON users.zip = zipcodes.zip)
      WHERE (username='Tester1')
    ) center, zipcodes) INNER JOIN users ON zipcodes.zip = users.zip
WHERE (users.username <> 'Tester1')
HAVING (distance < 5000)
ORDER BY distance;

请注意，您需要倒数第三行来过滤掉您为其计算距离的用户。如果您不这样做，结果将包含一行用户本身(在本例中为:Tester1)和(接近)0 的距离。 .

编辑 3

以下是以下评论要求的版本(所需的搜索半径存储为表 seekingdistance 中的附加字段 users):

SELECT zipcodes.zip,
       users.username,
       center.seekingdistance,
       (3959 * acos(cos(radians(zipcodes.latitude)) *
                    cos(radians(center.latitude)) *
                    cos(radians(zipcodes.longitude ) -
                        radians(center.longitude)) +
                    sin(radians(zipcodes.latitude)) *
                    sin(radians(center.latitude)))) AS distance FROM
  ( ( SELECT users.username,
             users.zip,
             users.seekingdistance,
             zipcodes.latitude,
             zipcodes.longitude FROM
      (users JOIN zipcodes ON users.zip = zipcodes.zip)
      WHERE (username='Tester1')
    ) center, zipcodes) INNER JOIN users ON zipcodes.zip = users.zip
WHERE (users.username <> 'Tester1')
HAVING (distance < center.seekingdistance)
ORDER BY distance;