SQL - 找到与提供的数字最接近的数字对 [英] SQL - find the closest pair of numbers to a provided pair

问题描述

我有一个数据库表，我想在其中选择与提供的一对值(无论是正值还是负值)最匹配的条目.

I have a DB Table where I would like to select the entry which is the closest match to a pair of provided values, whether positive or negative.

提供的值:

 num1 = 2.5, num2 = 10.2

相比:

[0] num1 = 1.1, num2 = 11.0
[1] num1 = 2.5, num2 = 14.1
[2] num1 = 2.4, num2 = 10.5
[3] num1 = 3.2, num2 = 10.3

我要查找的查询将返回行 [2].事件虽然有几个更接近的值，但最接近的一对将是 [2].

The query I am looking for would return row [2]. Event though there are a couple closer values, the closest pair would be [2].

我想在 SQL 选择语句中处理这个问题.这是我所拥有的，但在比较两对数字时还不够.

I would like to handle this in an SQL select statement. Here is what I have but it is not quite getting there in comparing both pairs of numbers.

(SELECT * FROM table WHERE num1 < 2.5 ORDER BY num1 DESC LIMIT 1)
  UNION ALL
(SELECT * FROM table WHERE num1 >= 2.5 ORDER BY num1 LIMIT 1)
  UNION ALL
(SELECT * FROM table WHERE num2 < 10.2 ORDER BY num2 DESC LIMIT 1)
  UNION ALL
(SELECT * FROM table WHERE num2 >= 10.2 ORDER BY num2 LIMIT 1)

这给了我 4 场接近的比赛，但不是最接近的一场比赛.我确定我的语法遗漏了一些东西，但我没有找到返回单个最接近匹配项的技巧.

This is giving me 4 close matches, but not the single closest match. I am sure my syntax is missing something, but I am not finding the trick to returning the single, closest match.

感谢所有想法.

推荐答案

您只需按目标和记录之间的聚合(或您喜欢的任何度量)绝对距离对表进行排序:

You simply need to sort the table by the aggregate (or whatever metric you prefer) absolute distance between the target and the records:

SELECT   *
FROM     my_table
ORDER BY ABS(2.5-num1) + ABS(10.2-num2)
LIMIT    1

在 sqlfiddle 上查看.

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