根据与给定值最近的值从数据库中查找行

I want to find table row from database depending on the value which is nearest to the given value.

我希望根据与给定值最接近的值从数据库中查找表行。

I have following data

我有以下数据

Id  Rate         Fat
1   10           8.00
2   20           8.10
3   30           8.20
4   40           8.30
5   50           8.34
6   60           8.40
7   50           8.36

suppose user want to find using the Fat

假设用户想要使用脂肪

For 8.0 it should return

对于8.0，它应该返回

Id  Rate         Fat
1   10           8.00

For 8.06 it should return

8.06美元应该会回来

Id  Rate         Fat
2   20           8.10

For 8.35 it should return 8.34 instead 8.36 (though difference is same it should give preference to lower value if difference is same)

对于8.35，它应该返回8.34而不是8.36(虽然差异是相同的，但是如果差异是相同的，那么它应该优先选择更低的值)

Id  Rate         Fat
5   60           8.34

1 个解决方案

#1

In SQL Server you could do this

在SQL Server中，您可以这样做

select top 1 *
from T
order by abs(Fat - 8.35), Fat

I guess that syntax for MySQL is

我猜MySQL的语法是

select *
from T
order by abs(Fat - 8.35), Fat
limit 1

#1