在 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.DENSE_RANK 给出两行并列第一的排名为 1，然后分配下一行的排名为 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.
相关文章