Выберите ближайшее числовое значение с запросом MySQL

Это, вероятно, проще, чем я это делаю, но в основном то, что мне нужно сделать, это выбрать строку, которая имеет самое близкое число в столбце в качестве заданного значения. Например:

Список значений в базе данных для трех строк в указанном столбце: 10, 15, 16

Если я укажу, что мне нужна строка, ближайшая к 14, она будет выбирать строку с 15.

Кроме того, если есть 2+ строки, которые имеют одинаковое расстояние, выберите один из них случайным образом.

Ответы

Ответ 1

Один вариант может быть следующим:

select   the_value,
         abs(the_value - 14) as distance_from_test
from     the_table
order by distance_from_test
limit 1

Чтобы выбрать случайную запись, вы можете добавить , rand() в предложение order by. Недостатком этого метода является то, что вы не получаете никакой выгоды от индексов, потому что вам нужно сортировать производное значение distance_from_test.

Если у вас есть индекс на the_value, и вы расслабляете свое требование, чтобы результат был случайным в случае связей, вы можете выполнить пару запросов с ограниченным диапазоном, чтобы выбрать первое значение, непосредственно выше тестового значения, и первое значение сразу ниже тестового значения и выбирается в зависимости от того, что ближе всего к тестовому значению:

(
select   the_value
from     the_table
where    the_value >= 14
order by the_value asc
limit 1
)
union
(
select   the_value
from     the_table
where    the_value < 14
order by the_value desc
limit 1
)
order by abs(the_value - 14)
limit 1

Ответ 2

Чтобы использовать индекс, вы можете выбрать минимальное значение выше цели и максимальное значение ниже. Тогда у вас есть только два значения для проверки.

Ответ 3

Как бы вы справились с тай-брейкерами? Потому что это займет только первое:

  SELECT t.col
    FROM TABLE t
ORDER BY ABS(t.col - @val)
   LIMIT 1

Альтернативная версия индекса:

  SELECT xt.col
    FROM (SELECT t.col,
                 ABS(t.col - @val) 'diff'
            FROM TABLE t) xt
ORDER BY xt.diff
   LIMIT 1