SQL - 查找与给定参数最接近的价格 | 查找与给定参数最接近的价格

本文介绍了SQL - 查找与给定参数最接近的价格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图找到价格最接近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.

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 - 查找与给定参数最接近的价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！