SQL - 查找与给定参数最接近的价格 [英] SQL - Find the closest price to a given argument

问题描述

我试图找到价格最接近SQL中给定参数的元组。
例如：参数price = 6将返回id 1和2.
参数price = 20将返回id 3。

I am trying to find the tuples which price is closest to a given parameter in SQL. Forexample: the parameter price = 6 would return id 1 and 2. Parameter price = 20 would return id 3.

p>

Table :

ID     PRICE
1      5
2      5
3      10

SELECT id 
FROM Table
WHERE table.price ?? 6

任何想法如何做？

谢谢。

推荐答案

SELECT ID
FROM TableX
WHERE PRICE = @CheckPrice +
      ( SELECT MIN(ABS(PRICE - @CheckPrice))
        FROM TableX
      )
   OR PRICE = @CheckPrice -
      ( SELECT MIN(ABS(PRICE - @CheckPrice))
        FROM TableX
      )

或（可能更好的性能）：

or (probably better for performance):

SELECT ID
FROM 
    TableX
  CROSS JOIN
          ( SELECT 
                ( SELECT MIN(PRICE) - @CheckPrice
                  FROM TableX
                  WHERE PRICE >= @CheckPrice
                ) AS Pover 
              , ( SELECT @CheckPrice - MAX(PRICE)
                  FROM TableX
                  WHERE PRICE <= @CheckPrice
                ) AS Punder
            FROM dual
          ) AS tmp 
WHERE PRICE IN ( @CheckPrice + LEAST(Pover, Punder)
               , @CheckPrice - LEAST(Pover, Punder)
               )

这篇关于SQL - 查找与给定参数最接近的价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！