题:表"point_2d"中有两列"x"和"y",分别表示一个点的横纵坐标,该表中每个点的坐标(一对)都是唯一的。
x | y |
---|---|
-1 | -1 |
0 | 0 |
-1 | -2 |
问:如何找到表中所有点之间的最小距离?(保留两位小数)
例子:上表中,最短距离是1.00。因为点(-1, -1)和(-1, -2)的距离为
所以最后的输出结果为:
shortest |
---|
1.00 |
**思路:根据上面的距离公式,我们发现我们要计算的是两个点之间的距离,这需要用到一对点的横纵坐标。所以对于一个点(x_1, y_1),我们需要计算该点与其他点的距离。这可以用"point_2d as p1 join "point_2d" as p2 on p1.x != p2.x or p1.y != p2.y" 来实现。注意,join从句中用的是"or",因为只要横或纵坐标中有一个与当前点不同,那就不是同一个点。**上面的语句表现为:
p1.x | p1.y | p2.x | p2.y |
---|---|---|---|
-1 | -1 | 0 | 0 |
-1 | -1 | -1 | -2 |
0 | 0 | -1 | -1 |
0 | 0 | -1 | -2 |
-1 | -2 | -1 | -1 |
-1 | -2 | 0 | 0 |
然后,我们很自然地想到对上表中的每一对点求距离:
SELECT p1.x, p1.y, p2.x, p2.y,
SQRT(POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)) as distance
FROM point_2d AS p1
JOIN point_2d AS p2 ON p1.x != p2.x OR p1.y != p2.y
p1.x | p1.y | p2.x | p2.y | distance |
---|---|---|---|---|
-1 | -1 | 0 | 0 | 1.41421 |
-1 | -1 | -1 | -2 | 1.0 |
0 | 0 | -1 | -1 | 1.41421 |
0 | 0 | -1 | -2 | 2.23607 |
-1 | -2 | -1 | -1 | 1.0 |
-1 | -2 | 0 | 0 | 2.23607 |
最终,我们只需要从上面的表中找到最小的"distance"值,并保留两位小数即可:
SELECT p1.x, p1.y, p2.x, p2.y,
ROUND(SQRT(MIN(POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2))), 2) AS shortest
FROM point_2d AS p1
JOIN point_2d AS p2 ON p1.x != p2.x OR p1.y != p2.y