從 MySQL 表中查找最接近的值

我想從下表中獲取給定值的最接近的費率。

mysql> select * from rates;
+------+----------+----------+
| ID   |   Type   |   Rate   |
+------+---------------------+
| 1    |    A     |    12    |
| 2    |    A     |    18    |
| 3    |    A     |    25    |
| 4    |    A     |    27    |
| 5    |    A     |    34    |
| 6    |    A     |    38    |
| 7    |    B     |    40    |
| 8    |    B     |    43    |
| 9    |    B     |    50    |
| 10   |    A     |    55    |
| 11   |    A     |    58    |
| 12   |    A     |    62    |
+------+---------------------+

例如，我想獲得最接近12它的利率應該返回12, 18, 25，同樣最接近的25是18,25,27

我嘗試了很多方法，但都不成功。

SELECT ID,
      TYPE,
      Rate
FROM (
       (SELECT *
        FROM
          (SELECT *
           FROM rates
           ORDER BY Rate DESC) a
        WHERE a.Type ='A'
          AND a.Rate <=25 LIMIT 0 ,
                                1)
     UNION
       (SELECT *
        FROM
          (SELECT *
           FROM rates
           ORDER BY Rate ASC) b
        WHERE b.Type ='A'
          AND b.Rate >25 LIMIT 0 ,
                               3)) r
GROUP BY r.Rate
ORDER BY r.Rate ASC LIMIT 3

它給了我25, 27, 34，但實際上它應該還給我18,25,27。有什麼建議？

SELECT id, type, rate
FROM
( ( SELECT id, type, rate, 25-rate AS diff
   FROM rates
   WHERE type = 'A'
     AND rate < 25
   ORDER BY rate DESC
     LIMIT 3
 ) 
 UNION ALL
 ( SELECT id, type, rate, rate-25 AS diff
   FROM rates
   WHERE type = 'A'
     AND rate >= 25
   ORDER BY rate ASC
     LIMIT 3
 ) 
) AS tmp
ORDER BY diff
LIMIT 3 ;

在**SQL-Fiddle進行測試**

我猜可以做得更簡單；

select id, type, rate, abs(25 - rate) as delta
from rates
order by delta
LIMIT 3

引用自：https://dba.stackexchange.com/questions/52223