在数据库中查找最接近的数值

I need to find a select statement that will return either a record that matches my input exactly, or the closest match if an exact match is not found.

我需要找到一个select语句，它将返回与我的输入完全匹配的记录，或者如果找不到完全匹配则返回最接近的匹配。

Here is my select statement so far.

到目前为止，这是我的选择声明。

SELECT * FROM [myTable] 
WHERE Name = 'Test' AND Size = 2 AND PType = 'p' 
ORDER BY Area DESC

What I need to do is find the closest match to the 'Area' field, so if my input is 1.125 and the database contains 2, 1.5, 1 and .5 the query will return the record containing 1.

我需要做的是找到与'Area'字段最接近的匹配，所以如果我的输入是1.125并且数据库包含2,1.5,1和.5，则查询将返回包含1的记录。

My SQL skills are very limited so any help would be appreciated.

我的SQL技能非常有限，所以任何帮助都会受到赞赏。

8 个解决方案

#1

get the difference between the area and your input, take absolute value so always positive, then order ascending and take the first one

获取区域和输入之间的差异，取绝对值，使其始终为正，然后按顺序递增并取第一个

SELECT TOP 1 * FROM [myTable] 
WHERE Name = 'Test' and Size = 2 and PType = 'p'
ORDER BY ABS( Area - @input )

#2

something horrible, along the lines of:

一些可怕的东西，沿着：

ORDER BY ABS( Area - 1.125 ) ASC LIMIT 1

Maybe?

也许？

#3

If you have many rows that satisfy the equality predicates on Name, Size, and PType columns then you may want to include range predicates on the Area column in your query. If the Area column is indexed this could allow efficient index-based access.

如果有许多行满足Name，Size和PType列上的等式谓词，那么您可能希望在查询的Area列中包含范围谓词。如果区域列被索引，则可以允许基于索引的有效访问。

The following query (written using Oracle syntax) uses one branch of a UNION ALL to find the record with minimal Area >= your target, while the other branch finds the record with maximal Area < your target. One of these two records will be the record that you are looking for. Then you can ORDER BY ABS(Area - ?input) to pick the winner out of those two candidates. Unfortunately the query is complex due to nested SELECTS that are needed to enforce the desired ROWNUM / ORDER BY precedence.

以下查询（使用Oracle语法编写）使用UNION ALL的一个分支查找具有最小Area> =您的目标的记录，而另一个分支查找具有最大Area <您的目标的记录。这两个记录中的一个将是您正在寻找的记录。然后你可以通过order by abs（区域 - ？输入）从这两个候选人中挑选出胜利者。遗憾的是，由于嵌套的selects需要强制执行所需的rownum order by优先级，因此查询很复杂。< p>

SELECT *
FROM
  (SELECT * FROM
    (SELECT * FROM
       (SELECT * FROM [myTable]
         WHERE Name = 'Test' AND Size = 2 AND PType = 'p' AND Area >= ?target
         ORDER BY Area)
       WHERE ROWNUM < 2
     UNION ALL
     SELECT * FROM 
       (SELECT * FROM [myTable]
         WHERE Name = 'Test' AND Size = 2 AND PType = 'p' AND Area < ?target
         ORDER BY Area DESC)
       WHERE ROWNUM < 2)
     ORDER BY ABS(Area - ?target))
WHERE rownum < 2

A good index for this query would be (Name, Size, PType, Area), in which case the expected query execution plan would be based on two index range scans that each returned a single row.

此查询的良好索引将是（名称，大小，PType，区域），在这种情况下，预期的查询执行计划将基于每个返回单行的两个索引范围扫描。

#4

How about ordering by the difference between your input and [Area], such as:

如何通过输入和[区域]之间的差异进行排序，例如：

DECLARE @InputValue DECIMAL(7, 3)

SET @InputValue = 1.125

SELECT TOP 1 * FROM [myTable]
WHERE Name = 'Test' AND Size = 2 AND PType = 'p'
ORDER BY ABS(@InputValue - Area)

#5

SELECT * 
  FROM [myTable] 
  WHERE Name = 'Test' AND Size = 2 AND PType = 'p' 
  ORDER BY ABS(Area - 1.125)
  LIMIT 1

-- MarkusQ

- MarkusQ

#6

Note that although ABS() is supported by pretty much everything, it's not technically standard (in SQL99 at least). If you must write ANSI standard SQL for some reason, you'd have to work around the problem with a CASE operator:

请注意，虽然几乎所有东西都支持ABS（），但它在技术上并不标准（至少在SQL99中）。如果由于某种原因必须编写ANSI标准SQL，则必须使用CASE运算符解决该问题：

SELECT * FROM myTable
WHERE Name='Test' AND Size=2 AND PType='p'
ORDER BY CASE Area>1.125 WHEN 1 THEN Area-1.125 ELSE 1.125-Area END

#7

If using MySQL

如果使用MySQL

SELECT * FROM [myTable] ... ORDER BY ABS(Area - SuppliedValue) LIMIT 1

#8

-2

Select the min where [field] > your_target_value Select the max where [field] < your_target_value

选择min [where] [field]> your_target_value选择最大值[field]

#1