LeetCode 612.平面上的最短距离

数据准备

Create Table If Not Exists Point2D (x int not null, y int not null);
Truncate table Point2D;
insert into Point2D (x, y) values ('-1', '-1');
insert into Point2D (x, y) values ('0', '0');
insert into Point2D (x, y) values ('-1', '-2');

输入

需求

请你写一个 SQL 查询报告 Point2D 表中任意两点之间的最短距离。保留 2 位小数

分析

power(num, power); //幂运算
sqrt(num); //开平方
pow(p1.x-p2.x , 2)+pow(p1.y-p2.y , 2) 求出两点之间距离的平方
sqrt(pow(p1.x-p2.x , 2)+pow(p1.y-p2.y , 2)) 使用sqrt开平方,求出两点之间的距离
round(rn1,2) 对两点之间的距离四舍五入
min(round(rn1,2)) 求出两点之间距离的最小值

输出

1. 求出所有数据

select p1.x as p1x, p1.y as p1y, p2.x as p2x, p2.y as p2y,
   sqrt(pow(p1.x-p2.x , 2)+pow(p1.y-p2.y , 2)) as rn1
from Point2D p1 , point2d p2
where p1.x!=p2.x or (p1.x=p2.x and p1.y!=p2.y);

2. 求出需要的数据

with t1 as (
    select p1.x as p1x, p1.y as p1y, p2.x as p2x, p2.y as p2y,
       sqrt(pow(p1.x-p2.x , 2)+pow(p1.y-p2.y , 2)) as rn1
    from Point2D p1 , point2d p2
    where p1.x!=p2.x or (p1.x=p2.x and p1.y!=p2.y)
)
select min(format(rn1,2)) as shortest
from t1;