gpt4 book ai didi

使用 JOIN 或关联子查询与 TEMPORARY TABLE 进行 MySQL 查找

转载 作者:行者123 更新时间:2023-11-29 10:28:58 25 4
gpt4 key购买 nike

我有两个主表,providers' 和depots`。每个提供商都有一个强制性的第一位置。通过添加额外的“仓库”来提供额外的位置。 “etc”代表与提供者帐户相关的大量附加变量。为了简化起见,此处省略了额外的表信息和过滤器。

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) |
+---------+------------+------------+

提供商可能有零个或多个附加仓库。这些“位置”用于计算来自每个提供商的传入“工作”的距离。传统上,我使用 UNION 将 providersdepots 表连接起来形成一个表,我将其称为 provDeps

从提供商中选择 id、位置、0 AS depotIdUNION SELECT p.id, d.location d.id AS depotId FROM 提供商 p, depots d

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

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;

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

最佳答案

我认为您不一定需要这里的临时表或 View 。您的最终查询在这里看起来完全符合预期,我已在下面对其进行了修改。我看到的唯一问题是您使用 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;

关于使用 JOIN 或关联子查询与 TEMPORARY TABLE 进行 MySQL 查找,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47785507/

25 4 0