sql - 如何获得多列之间的2个最大值？

Question

我想弄清楚如何从 5 个字段中获得 2 个最高值。我知道 greatest函数，但我也不知道如何拉出第二高的值。

基本上，该表有 5 NUMBER类型字段。在这个例子中，最后两列是我想要的结果。

| Score1 | Score2 | Score3 | Score4 | Score5 | | Highest1_value | Highest2_value 
+--------+--------+--------+--------+--------+ +----------------+---------------
|    10  |    20  |    30  |    40  |   50   | |       50       |       40
|    20  |    20  |    12  |    17  |    0   | |       20       |       20
|     7  |     7  |     7  |     7  |   11.1 | |       11.1     |        7
|    10  |    10  |    10  |    10  |   10   | |       10       |       10

Answer 1

取消数据透视并使用 row_number获得每个 id 的前 2 个最高分。

select id
,max(case when rnum=1 then val end) as highest_1
,max(case when rnum=2 then val end) as highest_2
from (select id,score,val,row_number() over(partition by id order by val desc) as rnum
      from (select * from t --replace this with your tablename
            unpivot (val for score in (score1,score2,score3,score4,score5)) p
          ) tbl
      ) tbl
group by id