mysql - 使用计算字段在 GROUP BY 中进行排序

Question

我有一个查询，可以在给定纬度和经度的特定半径内检索位置实体。

我现在正在尝试调整该查询以返回特定半径内的用户列表，并在旁边显示他们最近的位置。我的问题是，如果我按用户 ID 分组，则无法对位置进行排序以仅返回最近的位置。

我试过关注 ypercube's advice关于使用让我走到这一步的子查询:

SET @mylon = -1.095414;
SET @mylat = 50.79486;

SELECT  u.*, dest.*
FROM    users AS u
JOIN    locations AS dest
ON      dest.id = 
        (
        SELECT  l.id, 3956 * 2 * ASIN(SQRT(POWER(SIN((@mylat - abs(l.latitude)) * pi()/180/2),
                2) + COS(@mylat * pi()/180) * COS(abs(l.latitude) *
                pi()/180) * POWER(SIN((@mylon - l.longitude) *
                pi()/180/2),2))) as distance
        FROM    locations AS l
        HAVING  distance < 0.5
        ORDER BY
                l.created DESC
        LIMIT 1
        )

但是，这会返回一个 SQL 错误，因为我的子查询返回了多个字段。

有什么解决办法吗？谢谢!

Answer 1

首先，您的子查询必须只返回一个应该与 dest.id 匹配的 ID。我没有看到您使用 GROUP BY，因此您应该将 HAVING 替换为 WHERE。我认为您可以直接说 WHERE (3956 * 2 * ASIN(.......) < 0.5 这样您就不会将该计算放在结果中而只留下 id

SET @mylon = -1.095414;
SET @mylat = 50.79486;

SELECT  u.*, dest.*
FROM    users AS u
JOIN    locations AS dest
ON      dest.id = 
    (
    SELECT  l.id
    FROM    locations AS l
    WHERE (3956 * 2 * ASIN(SQRT(POWER(SIN((@mylat - abs(l.latitude)) * pi()/180/2),
            2) + COS(@mylat * pi()/180) * COS(abs(l.latitude) *
            pi()/180) * POWER(SIN((@mylon - l.longitude) *
            pi()/180/2),2)))) < 0.5
    ORDER BY
            l.created DESC
    LIMIT 1
    )

要返回计算结果，您可以像这样创建一个临时表

SET @mylon = -1.095414;
SET @mylat = 50.79486;

SELECT  u.*, dest.*
FROM    users AS u
JOIN    (
    SELECT  l.id, l.created, (3956 * 2 * ASIN(SQRT(POWER(SIN((@mylat - abs(l.latitude)) * pi()/180/2),
        2) + COS(@mylat * pi()/180) * COS(abs(l.latitude) *
        pi()/180) * POWER(SIN((@mylon - l.longitude) *
        pi()/180/2),2)))) as distance
FROM    locations  ) as location_table_tmp
ON u.id = location_table_tmp.id
ORDER BY location_table_tmp.created DESC

我不确定语法是否正确，尝试稍微调整一下，希望它能工作