SQL:选择两列相减的最小值，并选择最小相减中使用的数字

Question

我有两列，我想选择:价格、最小绝对值。价格和行使价之间的差异，行使价对应于最小绝对值。差值计算。我是 SQL 新手，如果这很简单，请原谅我。

Price   Strike
30.8    29
30.8    30
30.8    31
30.2    29
30.2    30
30.2    31

我想得到的答案是:

Price  Diff  Strike
30.8   .2    31
30.2   .2    30

到目前为止我的代码是:

Select min(price) as 'Price',
min(abs(price - Strike)) as 'Diff'
from Table
group by price

我不知道如何正确选择相应的罢工。

谢谢您

Answer 1

我将使用窗口函数来执行此操作。

SELECT Price,
       Diff,
       Strike
FROM   (SELECT *,
               Abs(price - Strike) Diff,
               Row_number()OVER(partition BY price ORDER BY Abs(price - Strike) )rn
        FROM   Yourtable) a
WHERE  rn = 1 

或者，如果每个 price 组的 Abs(price - Strike) 最小值存在平局，并且如果希望所有行具有相同的最小值，则使用 Dense_Rank

SELECT Price,
       Diff,
       Strike
FROM   (SELECT *,
               Abs(price - Strike) Diff,
               Dense_Rank()OVER(partition BY price ORDER BY Abs(price - Strike) )rn
        FROM   Yourtable) a
WHERE  rn = 1