1 题目描述
Point2D 表：
+-------------+------+
| Column Name | Type |
+-------------+------+
| x | int |
| y | int |
+-------------+------+
(x, y) 是该表的主键列(具有唯一值的列的组合)。 这张表的每一行表示 X-Y 平面上一个点的位置
p1(x1, y1) 和 p2(x2, y2) 这两点之间的距离是 sqrt((x2 - x1)2 + (y2 - y1)2) 。
编写解决方案，报告 Point2D 表中任意两点之间的最短距离。保留 2 位小数
2 测试用例
输入：
Point2D table:
+----+----+
| x | y |
+----+----+
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |
+----+----+
输出：
+----------+
| shortest |
+----------+
| 1.00 |
+----------+
解释： 最短距离是 1.00 ，从点 (-1, -1) 到点 (-1, -2)
3 解题思路
MySQL 中数学函数详情可以查看 MySQL Mathematical Functions
3.1 order by + limit
- 计算坐标的所有组合，按照题目提示计算坐标距离
select p2.x, p2.y, p1.x, p1.y, round(sqrt(pow(p2.x - p1.x, 2) + pow(p2.y - p1.y, 2)), 2) as shortest
from Point2D as p1
join Point2D as p2
where p1.x != p2.x
or p1.y != p2.y
执行结果
+--+--+--+--+--------+
|x |y |x |y |shortest|
+--+--+--+--+--------+
|-1|-1|-1|-2|1 |
|-1|-1|0 |0 |1.41 |
|0 |0 |-1|-2|2.24 |
|0 |0 |-1|-1|1.41 |
|-1|-2|0 |0 |2.24 |
|-1|-2|-1|-1|1 |
+--+--+--+--+--------+
- 对所有组合的坐标距离进行排序，通过分页获取第一条数据既距离最近的数据
select round(sqrt(pow(p2.x - p1.x, 2) + pow(p2.y - p1.y, 2)), 2) as shortest
from Point2D as p1
join Point2D as p2
where p1.x != p2.x
or p1.y != p2.y
order by shortest asc
limit 0, 1;
执行结果
+--------+
|shortest|
+--------+
|1 |
+--------+
3.2 min
- 计算坐标的所有组合，按照题目提示计算坐标距离，使用
min计算最小值
select round(sqrt(min(pow(p2.x - p1.x, 2) + pow(p2.y - p1.y, 2))), 2) as shortest
from Point2D as p1
join Point2D as p2
where p1.x != p2.x
or p1.y != p2.y;
执行结果
+--------+
|shortest|
+--------+
|1 |
+--------+
