在Oracle中选择第二小的最小值 [英] Select second most minimum value in Oracle

问题描述

我需要编写一个查询，该查询从整数列表中选择一个最小值和第二个最小值.

I need to write a query that selects a minimum value and it's second most minimum value from a list of integers.

抢夺最小值很明显:

select min(value) from table;

但是第二个最小不是那么明显.

But the second smallest is not so obvious.

为便于记录，此整数列表不是连续的-最小值可以是1000，第二个最小值可以是10000.

For the record, this list of integers is not sequential -- the min can be 1000, and the second most min can be 10000.

推荐答案

使用分析功能

SELECT value
  FROM (SELECT value,
               dense_rank() over (order by value asc) rnk
          FROM table)
 WHERE rnk = 2

分析函数RANK，DENSE_RANK和ROW_NUMBER相同，除了它们处理关系的方式相同. RANK使用打破领带的运动风格过程，因此如果两行并列排名为1，则下一行的排名为3.将下一行的排名分配为2.ROW_NUMBER任意打破平局，并给最低值的两行之一分配等级1，将另一行分配给等级2.

The analytic functions RANK, DENSE_RANK, and ROW_NUMBER are identical except for how they handle ties. RANK uses a sports-style process of breaking ties so if two rows tie for a rank of 1, the next row has a rank of 3. DENSE_RANK gives both of the rows tied for first place a rank of 1 and then assigns the next row a rank of 2. ROW_NUMBER arbitrarily breaks the tie and gives one of the two rows with the lowest value a rank of 1 and the other a rank of 2.

这篇关于在Oracle中选择第二小的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！