SQL - 寻找最近的价格给定的参数(SQL - Find the closest price t

我试图找到其中的价格是最接近SQL定参数的元组。 Forexample：参数价格= 6将返回ID 1和2参数价= 20将返回ID 3。

表：

ID     PRICE
1      5
2      5
3      10

SELECT id 
FROM Table
WHERE table.price ?? 6

任何想法如何做到这一点？

谢谢。

Answer 1:

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

或（可能获得更好的性能）：

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)
               )

Answer 2:

SELECT id, price
FROM thetable t1
WHERE NOT EXISTS ( SELECT *
   FROM thetable t2
   WHERE abs (t2.price - 6) < abs (t1.price - 6)
   );

Answer 3:

该查询使用子查询选择最接近的价格，然后返回匹配的价格，所有行：

SELECT *
FROM Table
WHERE price = ( --could also use "WHERE price in" here...
    SELECT TOP 1 price FROM Table
    GROUP BY price
    ORDER BY Min(Abs(price - ?)))

Answer 4:

我们使用UNION子查询获得最接近的价格目标价。 在每个子查询中我们得到和ORDER（各型号价格和目标价格之间的差异）的绝对三角洲到底三角洲。 工作得又快又稳。 希望能帮助到你 ;）

SET @CheckPrice = 3910;

(
    SELECT
        m_id,
        m_name,
        m_auto_price,
        m_auto_discount,
        ABS(m_auto_price - @CheckPrice)AS delta
    FROM
        s_models
    WHERE
        m_auto_price >= @CheckPrice
    ORDER BY
        m_auto_price ASC
    LIMIT 10
)
UNION
    (
        SELECT
            m_id,
            m_name,
            m_auto_price,
            m_auto_discount,
            ABS(m_auto_price - @CheckPrice)AS delta
        FROM
            s_models
        WHERE
            m_auto_price <= @CheckPrice
        ORDER BY
            m_auto_price DESC
        LIMIT 10
    )
ORDER BY
    delta ASC,
    m_auto_price DESC,
    m_auto_discount DESC

文章来源: SQL - Find the closest price to a given argument