代码之家 › 专栏 › 技术社区 › NickSaintJohn

使用连接或关联子查询与临时表进行MySQL查找

temp-tables inner-join database mysql

NickSaintJohn · 技术社区 · 7 年前

我有两个主表, providersâ and 仓库`。每个提供者都有一个强制的第一个位置。通过添加额外的“仓库”提供额外的位置etc’表示与提供商帐户相关的大量附加VAR。为了简化,这里省略了额外的表信息和过滤器。

providers
+--------+------------+-----+
| id     | location   | etc |
+--------+------------+-----+
| 1      | POINT(1,1) | ... |
| 2      | POINT(1,2) | ... |
| 3      | POINT(1,3) | ... |
+--------+------------+-----+

depots
+---------+------------+------------+
| depotId | providerId | location   |
+---------+------------+------------+
| 1       | 1          | POINT(2,1) |
| 2       | 1          | POINT(2,2) |
| 3       | 1          | POINT(2,3) |
| 4       | 2          | POINT(2,4) |
| 5       | 2          | POINT(2,5) |
+---------+------------+------------+

提供商可能有零个或多个附加站点。这些“位置”用于计算传入作业与每个提供者的距离。传统上,我使用工会来加入 providers 和 depots 表以形成单个表,我将其称为 provDeps .

从提供程序中选择id、位置、0作为depotId 联合选择p.id、d.location d.id作为提供商p、仓库d的仓库id

让我们假设这是一个视图,暂时放弃效率和索引。它有望降低查询的视觉复杂性。

provDeps
+--------+------------+---------+-----+
| id     | location   | depotId | etc |
+--------+------------+---------+-----+
| 1      | POINT(1,1) | 0       | ... |
| 1      | POINT(2,1) | 1       | ... |
| 1      | POINT(2,2) | 2       | ... |
| 1      | POINT(2,3) | 3       | ... |
| 2      | POINT(1,2) | 0       | ... |
| 2      | POINT(2,4) | 4       | ... |
| 2      | POINT(2,5) | 5       | ... |
| 3      | POINT(1,3) | 0       | ... |
+--------+------------+---------+-----+

然后,我使用 provDeps公司 . 这里的想法是计算作业到每个仓库的距离。这是通过存储过程执行的。

SELECT loc.*, degToMeter(st_distance(jobLocation, location)) AS distanceToJob FROM provDeps;

+--------+------------+---------+---------------+-----+
| id     | location   | depotId | distanceToJob | etc |
+--------+------------+---------+---------------+-----+
| 1      | POINT(1,1) | 0       | 8234          | ... |
| 1      | POINT(2,1) | 1       | 7334          | ... |
| 1      | POINT(2,2) | 2       | 6434          | ... |
| 1      | POINT(2,3) | 3       | 5534          | ... |
| 2      | POINT(1,2) | 0       | 4634          | ... |
| 2      | POINT(2,4) | 4       | 3734          | ... |
| 2      | POINT(2,5) | 5       | 2834          | ... |
| 3      | POINT(1,3) | 0       | 1934          | ... |
+--------+------------+---------+---------------+-----+

我现在需要减少此列表,以仅保留最近的仓库,并按提供商id分组。结果将包括每个提供商,但每个提供商只有一个仓库-主位置、仓库“0”或最近仓库的id。这是期望的结果:-

+--------+------------+---------+---------------+-----+
| id     | location   | depotId | distanceToJob | etc |
+--------+------------+---------+---------------+-----+
| 1      | POINT(2,3) | 3       | 5534          | ... |
| 2      | POINT(2,5) | 5       | 2834          | ... |
| 3      | POINT(1,3) | 0       | 1934          | ... |
+--------+------------+---------+---------------+-----+

我尝试了很多方法,但每种方法都遇到了不同的问题。我最接近成功的方法是使用临时表:-

DROP TEMPORARY TABLE IF EXISTS locTemp;
CREATE TEMPORARY TABLE locTemp AS
    SELECT depots.*, st_distance(jobLocation, location) AS distanceToJob
    FROM provDeps

然后,我尝试使用相关子查询,但这会导致在一次查找中尝试两次访问临时表的错误:-

SELECT * FROM locTemp
WHERE distanceToJob = (SELECT MIN(distanceToJob) FROM locTemp AS lt WHERE lt.id = locTemp.id);

这将导致错误“无法重新打开表:'locTemp'”。我也尝试过执行联接,但随后在子查询中出现分组错误,或无法访问临时表本身:-

SELECT * FROM
(
    SELECT id, depotId, MIN(distanceToJob) as minDist
    FROM locTemp GROUP BY id
) AS res
INNER JOIN locTemp AS lt on lt.id = res.id and lt.minDist = res.distanceToJob;

任何指点,或更好的解决方案,都将不胜感激!:)

1 回复 | 直到 7 年前

Tim Biegeleisen 7 年前

我认为您不一定需要临时表或视图。您的最终查询在这里看起来完全正常,我在下面对其进行了修改。我看到的唯一问题是,您选择了一个非聚合列 GROUP BY 也许使用临时表有问题。

SELECT
    t1.id, t1.location, t1.depotId,
    degToMeter(st_distance(t1.jobLocation, t1.location)) AS distanceToJob
FROM provDeps t1
INNER JOIN
(
    SELECT
        id,
        MIN(degToMeter(st_distance(t1.jobLocation, t1.location))) AS minDistanceToJob
    FROM provDeps
    GROUP BY id
) t2
    ON t1.id = t2.id AND
       degToMeter(st_distance(t1.jobLocation, t1.location)) = t2.minDistanceToJob;