-
Notifications
You must be signed in to change notification settings - Fork 0
/
612. Shortest Distance in a Plane
58 lines (45 loc) · 1.28 KB
/
612. Shortest Distance in a Plane
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
612. Shortest Distance in a Plane
Solved
Medium
Topics
SQL Schema
Pandas Schema
Table: Point2D
+-------------+------+
| Column Name | Type |
+-------------+------+
| x | int |
| y | int |
+-------------+------+
(x, y) is the primary key column (combination of columns with unique values) for this table.
Each row of this table indicates the position of a point on the X-Y plane.
The distance between two points p1(x1, y1) and p2(x2, y2) is sqrt((x2 - x1)2 + (y2 - y1)2).
Write a solution to report the shortest distance between any two points from the Point2D table. Round the distance to two decimal points.
The result format is in the following example.
Example 1:
Input:
Point2D table:
+----+----+
| x | y |
+----+----+
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |
+----+----+
Output:
+----------+
| shortest |
+----------+
| 1.00 |
+----------+
Explanation: The shortest distance is 1.00 from point (-1, -1) to (-1, 2).
------------------------------------------------------
# Write your MySQL query statement below
with cte as (Select pt.x as x1,pt.y as y1,pt1.x as x2 , pt1.y as y2
from Point2D pt
cross join Point2D pt1)
, cte2 as (select *, round(sqrt(power((x2-x1),2) + power( (y2-y1),2)),2) as pt11
from cte)
select min(pt11) as shortest
from cte2
where pt11>0