使用MySQL查询选择最接近的数值 [英] Select closest numerical value with MySQL query

问题描述

这可能比我做的要容易，但是基本上我需要做的是选择一列中具有最接近数字的行作为指定值.例如:

This is probably easier than I am making it, but basically what I need to do is select the row that has the closest number in a column as a specified value. For example:

数据库中指定列中的3行的值列表:10、15、16

List of values in database for 3 rows in a specified column: 10, 15, 16

如果我指定我想要最接近14的行，它将选择15的行.

If I specify that I want the row that is closest to 14, it would pick the row with 15.

此外，如果有2条以上相同距离的行，则随机选择其中之一.

Also, if there are 2+ rows that are the same distance, pick one of them randomly.

推荐答案

一个选项可能类似于:

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进行排序.

To select a random record, you can add , rand() to the order by clause. The disadvantage of this method is that you don't get any benefit from indices because you have to sort on the derived value distance_from_test.

如果在the_value上具有索引，并且在平局的情况下放宽了对结果随机性的要求，则可以执行一对有限范围查询，以选择紧接测试值上方的第一个值，然后选择测试值正下方的第一个值，然后选择最接近测试值的那个值:

If you have an index on the_value and you relax your requirement for the result to be random in the case of ties, you can perform a pair of limited range queries to select the first value immediately above the test value and the first value immediately below the test value and pick whichever is closest to the test 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

这篇关于使用MySQL查询选择最接近的数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！