MySQL 如何从mysql中的数据库中获取最近的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7269243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get nearest value from database in mysql
提问by Kanishka Panamaldeniya
I am using mySQLand CodeIgniter. I have some floating point numbers in my database such as
我正在使用mySQL和CodeIgniter。我的数据库中有一些浮点数，例如
- 8.3456
- 8.5555
- 4.5556
- 8.3456
- 8.5555
- 4.5556
I want to...
我想要...
SELECT * FROM table WHERE value = $myvalue
but I can't use value = $myvaluein my SELECT query because $myvalueis not exactly equal to database values. I need to get the nearest value to $myvaluefrom database.
但我不能value = $myvalue在我的 SELECT 查询中使用，因为$myvalue它不完全等于数据库值。我需要$myvalue从数据库中获取最接近的值。
If $myvalueis 5 I want to select the value 4.5556.
如果$myvalue是 5 我想选择值4.5556。
How can I do this in mySQL?
我怎样才能在 mySQL 中做到这一点？
回答by Bohemian
select *
from table
order by abs(value - $myvalue)
limit 1
回答by p.marino
Assuming that you have a 10% tolerance (+/-) you could try something like:
假设您有 10% 的容差 (+/-)，您可以尝试以下操作：
select * from table
where value >= ($myvalue * .9) and value <= ($myvalue * 1.1)
order by abs(value - $myvalue) limit 1
Slightly updated stealing from others - this should return the nearest result in the assumed tolerance range. (Also, I just noticed the where was incorrect, apologies - now it should work).
稍微更新从他人那里窃取 - 这应该返回假设容差范围内最接近的结果。（另外，我刚刚注意到哪里不正确，抱歉 - 现在它应该可以工作了）。
回答by Graham Ritchie
(
select *
from table
where value >= $myvalue
order by value asc
limit 1
)
union
(
select *
from table
where value < $myvalue
order by value desc
limit 1
)
order by abs(value - $myvalue)
limit 1
This may look counter-intuitive but the speed will be greater than the other queries shown so far.
这可能看起来违反直觉，但速度将高于目前显示的其他查询。
This is due to the fact that a greater thanand less thanquery is quicker.
这是因为 a greater thanandless than查询更快。
Then doing an ABSon two values is nothing.
然后ABS在两个值上做一个没什么。
This will give you the quickest return in a single query I can think of.
这将在我能想到的单个查询中为您提供最快的回报。
Doing an ABSon a whole table will be slow as it will scan the whole table.
做一个ABS对整个表将是缓慢的，因为它会扫描整个表。
回答by SIDU
Get the largest value similar to $val:
获取类似于 $val 的最大值：
SELECT * FROM tab WHERE val <= $val ORDER BY val DESC LIMIT 1
Get the smallest value similar to $val:
获取类似于 $val 的最小值：
SELECT * FROM tab WHERE val >= $val ORDER BY val LIMIT 1
Get the closest value similar to $val in either direction:
在任一方向获取与 $val 相似的最接近值：
SELECT * FROM tab ORDER BY abs(val - $val) LIMIT 1
回答by Klas Lindb?ck
Take the first value from the following:
从以下取第一个值：
select * from table order by abs(value - $myvalue);
回答by Johan
SELECT * FROM table1 ORDER BY ABS(value - '$myvalue') LIMIT 1
回答by Vishal P Gothi
Try this:
尝试这个：
SELECT *,abs((columnname -Yourvalue)) as near
FROM table
WHERE order by near limit 0,1
回答by Andrew Lazarus
Unfortunately, I think your database will probably do a full table scan for solutions that involve abs, so they will be (very) slow once your table grows. A fast-running solution may be found in this earlier thread.
不幸的是，我认为您的数据库可能会对涉及 的解决方案进行全表扫描abs，因此一旦您的表增长，它们将（非常）缓慢。可以在这个较早的线程中找到快速运行的解决方案。
回答by UserMat
SELECT number, ABS( number - 2500 ) AS distance
FROM numbers
ORDER BY distance
LIMIT 6
回答by espradley
In my case, I was using the browsers geolocations and trying to find a closest city/state based on the coordinates I had in a table.
就我而言，我使用浏览器的地理位置并尝试根据我在表格中的坐标找到最近的城市/州。
table structure:
表结构：
id zipcode city_state lat lon
1 12345 Example, GA 85.3 -83.2
Recommend testing this vigorously before using -- probably needs some tweaks, but I came up with this as a start
建议在使用前大力测试——可能需要一些调整，但我想出了这个作为开始
SELECT city_state,
zipcode,
( Abs( lat - -33.867886 )
+ Abs( lon - -63.987) ) AS distance
FROM zipcodes
ORDER BY distance
LIMIT 1;
For laravel users:
对于 Laravel 用户：
$city = Zipcodes::selectRaw
('city_state, zipcode, ( ABS( lat - ? ) + ABS( lon - ?) ) AS distance', [$lat, $lon])
->orderBy('distance')
->first();
echo $city->city_state
Hope this helps someone someday.
希望有一天这对某人有所帮助。
