mysql - 根据子查询中返回的 id 对多个表进行连接并保持该顺序

Question

这是一个棘手的问题(或者我认为如此)。我有一个如下所示的存储过程:

CREATE DEFINER=`dev-user`@`%` PROCEDURE `geospacial`(IN _lat double, IN _lng double, IN _distance double, IN _limit int)
BEGIN

set @radius = _distance;
set @lng_min = _lng - @radius / abs(cos(radians(_lat)) * 111);
set @lng_max = _lng + @radius / abs(cos(radians(_lat)) * 111);
set @lat_min = _lat - (@radius / 111);
set @lat_max = _lat + (@radius / 111);

SELECT DISTINCT
    `project_id`

FROM (
    SELECT
         `project_id`,
         ROUND((6371 * ACOS(COS(RADIANS(_lat)) * COS(RADIANS(`lat`)) * COS(RADIANS(`long`) - RADIANS(_lng)) + SIN(RADIANS(_lat)) * SIN(RADIANS(`lat`)))), 2) AS distance

    FROM
        geodata

    WHERE
        (`long` BETWEEN @lng_min AND @lng_max)

    AND
        (`lat` BETWEEN @lat_min and @lat_max)

    ORDER BY
        CAST(distance as DECIMAL(10,5)) ASC
) as _geo

;END

调用时，它会返回点最接近给定纬度/经度的项目的 ID，尽管这些细节可能并不重要。鉴于我现在的结果，我是否能够围绕 SELECT DISTINCT... 查询创建一个外部查询，该查询将获取所有具有给定 id 的项目，并与基于项目编号。我知道我可以使用 WHERE IN，但我会放宽 ID 的顺序(顺序很重要)。

在一个请求中执行此操作的任何 SQL 方式？我能想到的最佳选择是将这些 ID 存储在临时表中，然后根据 project_id 对其进行选择/连接。

Answer 1

我认为你可以在外部查询中使用 group by 和 order by 做你想做的事:

SELECT project_id
FROM (SELECT project_id,
             ROUND((6371 * ACOS(COS(RADIANS(_lat)) * COS(RADIANS(`lat`)) * COS(RADIANS(`long`) - RADIANS(_lng)) + SIN(RADIANS(_lat)) * SIN(RADIANS(`lat`)))), 2) AS distanc
      FROM geodata
      WHERE (`long` BETWEEN @lng_min AND @lng_max) AND
            (`lat` BETWEEN @lat_min and @lat_max)
     ) gd
GROUP BY project_id
ORDER BY MIN(CAST(distance as DECIMAL(10,5))) ASC;