问题
point 表包含了一些点的x坐标值，坐标值均为整数。
写一条查询语句计算这些点中的最短距离。
x
-1
0
2
最短距离明显是1，是-1和0之间的距离。所以输出如下：
shortest
1
注意: 每个点都是唯一的， point 表中没有重复记录。
生成数据
CREATE TABLE POINT(X INT);
INSERT INTO POINT VALUE(-1),(0),(2),(4);
SELECT * FROM POINT;
解答
想法是 对x进行排序 就可以得到类似于数轴的关系 既然要求最近距离就可以只求相邻两个值的差值 再找最小即可
但是这样取需要引入id来确认他们是否相邻
尝试一下
SELECT X ,@num:=@num+1 AS num
FROM POINT, (SELECT @num:=0) AS tmp
ORDER BY X DESC;
把相邻两者连接 num1和num要做去区分
SELECT *
FROM (SELECT X ,@num:=@num+1 AS num
FROM POINT, (SELECT @num:=0) AS tmp
ORDER BY X DESC) A1,
(SELECT X ,@num1:=@num1+1 AS num1
FROM POINT, (SELECT @num1:=0) AS tmp
ORDER BY X DESC) A2
WHERE A1.num = A2.num1-1;
选出相邻之差的最大值即可
SELECT MAX(A1.X -A2.X) AS shortest
FROM (SELECT X ,@num:=@num+1 AS num
FROM POINT, (SELECT @num:=0) AS tmp
ORDER BY X DESC) A1,
(SELECT X ,@num1:=@num1+1 AS num1
FROM POINT, (SELECT @num1:=0) AS tmp
ORDER BY X DESC) A2
WHERE A1.num = A2.num1-1;
别的解答
构造偏序关系
select min(P2.x-P1.x) as `shortest`
from Point as P1 join Point as P2
on(P1.x < P2.x)
也可以把不等的两个数值拿来做差取绝对值的最大值
select min(abs(P2.x-P1.x)) as `shortest`
from Point as P1 join Point as P2
on(P1.x <> P2.x)
