sql - 第二个最大值和最小值

给定一个具有多行int字段和相同标识符的表，则可以从表中返回第二个最大值和第二个最小值。

一个表由

ID      |   number
------------------------
1       |     10
1       |     11
1       |     13
1       |     14
1       |     15
1       |     16

最终结果将是

ID      |   nMin    |   nMax
--------------------------------
1       |     11    |    15

最佳答案

您可以使用row_number为每个ID分配排名。然后，您可以group by id并选择排名靠后的行。以下示例选择了第二个最低和第三个最高:

select  id
,       max(case when rnAsc = 2 then number end) as SecondLowest
,       max(case when rnDesc = 3 then number end) as ThirdHighest
from    (
        select  ID
        ,       row_number() over (partition by ID order by number) as rnAsc
        ,       row_number() over (partition by ID order by number desc) as rnDesc
        ) as SubQueryAlias
group by
        id

max只是选择一个非空值；您可以将其替换为min甚至avg，它不会影响结果。