mysql - 找到最接近的值 - MySQL

Question

如何找到多个值中最接近的值？

例如:我有这张 table

A 1

B 2

C 3

D 7

E 11

F 12

所以 -

for A i want to get B, 
for B - A and C,
for C - B,
for D - E,
for E - F,
for F - E,

谢谢

Answer 1

试试这个:

SELECT t1.col1, t2.col1
FROM mytable AS t1
INNER JOIN mytable AS t2 
  ON t1.col1 <> t2.col1
LEFT JOIN mytable AS t3 
  ON t3.col1 <> t2.col1 AND t3.col1 <> t1.col1 AND 
     ABS(t3.col2 - t1.col2) < ABS(t2.col2 - t1.col2)
WHERE t3.col1 IS NULL  
ORDER BY t1.col1

Demo here