테이블과 결과
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).
문제
The distance between two points p1(x1, y1) and p2(x2, y2) is sqrt((x2 - x1)2 + (y2 - y1)2).
Write an SQL query to report the shortest distance between any two points from the Point2D table. Round the distance to two decimal points.
The query result format is in the following example.
답
SELECT
ROUND(SQRT(MIN(POW((pd2.x - pd1.x), 2) + POW((pd2.y - pd1.y), 2))), 2) AS 'shortest'
FROM Point2D pd1
JOIN Point2D pd2
ON pd1.x != pd2.x OR pd1.y != pd2.y
;