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

Question

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

一张表由

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

最终结果将是

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

Answer 1

您可以使用 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并且不会影响结果。