mysql - 使用mysql查询从表中选择最接近的值

Question

我有一个表，其中包含一些值，列 max_value

max_value     
    100            
    250            
    320           
    160  

所以我想通过检查下面列出的几个条件来选择最接近 max_value 的值:

1) 如果我有值 111 那么它应该只得到 max_value = 160

2) 如果我有 value=450 那么它应该得到 max_value = 320

3) 如果我有 value=350 那么它应该得到 320 因为没有大于 350 的值所以它应该获取它最近的较低值

Mysql查询:

SELECT * FROM `table` 
where max_value <='450' or max_value >='450' 
ORDER BY max_value ASC LIMIT 1.

Answer 1

一种方法是创建输入值和最大值之间的绝对差值，然后根据该值进行排序以获取绝对差值最低的行。但这将获得最接近的值，无论是高于还是低于输入数字。但是，您修改后的要求表明，如果有一个值刚好高于输入值，那么您会更喜欢它。

SELECT max_value 
FROM your_table 
ORDER BY 
  -- give first priority to values above 450
  CASE WHEN max_value >= 450 THEN 1 ELSE 2 END ASC
  ABS(450 - max_value) ASC 
LIMIT 1

但是，不幸的是，上述方法不能使用索引，并且在大表上会很慢。因此，要在 max_value 列上使用索引，我们可以在两个不同的 SELECT 查询上使用 UNION 来获得刚好高于 450 和刚好低于 450 的最接近值。因此请尝试以下操作:

SELECT dt.max_value FROM 
(
  (
    -- getting the closest value just above 450
    SELECT max_value, 1 AS priority
    FROM your_table 
    WHERE max_value >= 450   -- notice >=
    ORDER BY max_value ASC   -- ASC LIMIT 1 to get value just above
    LIMIT 1
  )

  UNION 

  (
    -- getting the closest value just below 450
    SELECT max_value, 2 
    FROM your_table 
    WHERE max_value <= 450    -- notice <=
    ORDER BY max_value DESC   -- DESC LIMIT 1 to get value just below
    LIMIT 1
  ) 
) dt 

-- first priority to get the closest value above, then only below
ORDER BY dt.priority ASC