mysql - 在同一个表中查找每行最接近的值

Question

假设我从“表 a”开始

+-----------+
|iD | Value |
|-----------|
| 0 |  1    |
| 1 |  5    |
| 2 |  6    |
| 3 |  8    |
+-----------+

然后我想创建一个 SQL 查询来显示 iD、值、数值上最接近当前值的值以及这些值之间的差异，例如

+-----------+------------+------------+
|iD | Value | closestVal | Difference |
|-----------|------------|------------+
| 0 |  1    |    5       |   4        |
| 1 |  5    |    6       |   1        |
| 2 |  6    |    5       |   1        |
| 3 |  8    |    6       |   2        |
+-----------+------------+------------+

根据研究，我认为它可能包括使用 ABS(a.Value - b.Value) 的东西，假设负数是可能的，尽管我不确定当前行和比较行的引用到底如何处理以及一个值的输出，而不是每次比较的值表。

我该怎么做呢？任何帮助将不胜感激

Answer 1

我认为最简单的方法是使用相关子查询:

select t.*, abs(closestvalue - value) as difference
from (select t.*,
             (select t2.value
              from tablea t2
              order by abs(t2.value - t.value) asc
              limit 1
             ) as closestvalue
      from tablea t
     ) t;

您也可以通过显式join 执行此操作，但逻辑有点奇怪:

select a.id, a.value,
       (case when min(case when a.value > a1.value then a.value - a1.value end) =  min(abs(a.value - a1.value))
             then a.value - min(abs(a.value - a1.value))
             else a.value + min(abs(a.value - a1.value))
        end) as closestvalue
       min(abs(a.value - a1.value)) as difference
from tablea a join
     tablea a1
     on a.id <> a1.id
group by a.id;

使用这种方法，通过使用 min(abs()) 很容易找到差异。然后问题是弄清楚最接近的值是大于还是小于该值。此处的逻辑使用条件聚合并将这些结果与实际最小值进行比较来计算。