如何使用 SQL Server 找到最接近零的值 [英] How to find the value closest to zero using SQL Server

问题描述

假设我有一个表格，其中可以包含各种数字，例如它可能如下所示:

Let's say I have a table that could have various numbers in it, for example it could look like the following:

示例A:我的表-10,-3,5、10、

ExampleA: MyTable -10, -3, 5, 10,

示例B:我的表-10,-5,3、10、

ExampleB: MyTable -10, -5, 3, 10,

因此，如果我查询 ExampleA 中的表，我希望它返回-3"(接近 0 的值)

So if I queried the table in ExampleA I'd want it to return "-3" (The value closet to 0)

同样，如果我查询 ExampleB 中的表，我希望它返回3"(最接近 0 的值)

Likewise if I queried the table in ExampleB I'd want it to return "3" (The value closest to 0)

无论表中有多少数字，我总是想找到最接近零的值，我该怎么做?

I always want to find the value that is closest to zero regardless of the numbers in the table, how can I do this?

此外，我该如何选择关系的值(例如最接近的值可能是 -3 和 3 的情况)?

Also, how could I choose which value for ties (like in the case where the closest value may be -3 and 3)?

推荐答案

使用 min() 和 abs() 的组合:

Use a combination of min() and abs():

select num
from mytable
where abs(num) = (select min(abs(num)) from mytable)

要打破平局，将 min() 或 max() 应用于 num 以获得负或正侧，例如

To break ties, apply min() or max() to num to get the negative or positive side, eg

获得平局的否定:

select min(num) num 
from mytable
where abs(num) = (select min(abs(num)) from mytable)

要获得平局:

select max(num) num
from mytable
where abs(num) = (select min(abs(num)) from mytable)

这篇关于如何使用 SQL Server 找到最接近零的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！