SQL 在数据库中查找最接近的数值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/592209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find closest numeric value in database
提问by Tester101
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.
我需要做的是找到与“区域”字段最接近的匹配项，因此如果我的输入是 1.125 并且数据库包含 2、1.5、1 和 0.5，则查询将返回包含 1 的记录。
My SQL skills are very limited so any help would be appreciated.
我的 SQL 技能非常有限，因此将不胜感激。
回答by MikeW
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 )
回答by Ryan Graham
something horrible, along the lines of:
一些可怕的东西，大致如下：
ORDER BY ABS( Area - 1.125 ) ASC LIMIT 1
Maybe?
也许？
回答by George Eadon
If you have many rows that satisfy the equality predicates on Name, Size, and PTypecolumns then you may want to include range predicates on the Areacolumn in your query. If the Areacolumn is indexed this could allow efficient index-based access.
如果您有许多行满足、 和列上的等式谓词Name，那么您可能希望在查询中的列上包含范围谓词。如果该列被索引，这可以允许有效的基于索引的访问。SizePTypeAreaArea
The following query (written using Oracle syntax) uses one branch of a UNION ALLto 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 语法编写）使用 a 的一个分支UNION ALL查找Area >=目标最少的记录，而另一个分支查找Area <目标最大的记录。这两个记录之一将是您要查找的记录。然后你可以ORDER BY ABS(Area - ?input)从这两个候选人中选出获胜者。不幸的是，由于执行所需的 ROWNUM / ORDER BY 优先级需要嵌套的 SELECTS，因此查询很复杂。
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.
此查询的一个很好的索引是(Name, Size, PType, Area)，在这种情况下，预期的查询执行计划将基于两个索引范围扫描，每个扫描都返回一行。
回答by MarkusQ
SELECT *
FROM [myTable]
WHERE Name = 'Test' AND Size = 2 AND PType = 'p'
ORDER BY ABS(Area - 1.125)
LIMIT 1
-- MarkusQ
——马库斯Q
回答by boflynn
How about ordering by the difference between your input and [Area], such as:
如何根据您的输入和 [Area] 之间的差异进行排序，例如：
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)
回答by Alnitak
If using MySQL
如果使用 MySQL
SELECT * FROM [myTable] ... ORDER BY ABS(Area - SuppliedValue) LIMIT 1
回答by bobince
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
回答by JustinCaribe
Select the min where [field] > your_target_value Select the max where [field] < your_target_value
选择最小值 where [field] > your_target_value 选择最大值 where [field] < your_target_value
