mysql 最接近数字_使用MySQL查询选择最接近的数值

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:

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

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

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

解决方案

One option would be something along the lines of:

select the_value,

abs(the_value - 14) as distance_from_test

from the_table

order by distance_from_test

limit 1

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.

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