如何在多列中获取最大的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

- Rocco

---

每一行都有一个标识符吗？ - Vamsi Prabhala

你的第二行 highest2_value 应该是 17，我猜对了吗？ - Teja

vkp，是的，我有唯一的ID。Teja，不，Score1和Score2都是20，所以它们是最高的数字。 - Rocco

期望输出应该是什么样子的？请将其作为格式化文本发布。 - Vamsi Prabhala

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

- Vamsi Prabhala

---

其他数据库不能不使用unpivot吗？MySQL可以吗？ - Teja

在MySQL中，您可以直接使用greatest或least获取max或min，而无需进行非标准化操作。由于OP需要2个最大值，我想不到其他方法，只能使用row_number。 - Vamsi Prabhala

这只返回Score4和Score5字段的值吗？ - Rocco

订购必须在 val 字段上而不是 score 上进行 row_number。我已经更改了它，现在应该按预期工作。 - Vamsi Prabhala

现在可以了，我接受这个答案，因为这是第一个。谢谢大家。 - Rocco

Answer 2

如果我理解正确，您需要这个：

select your_table.*, REGEXP_SUBSTR(h.str, '^[^\-]+') AS h1, REGEXP_SUBSTR(h.str, '[^\-]+$') AS h2  FROM your_table
inner join  (
    select id, listagg(sc, '-') WITHIN GROUP (ORDER BY sc DESC) str FROM(
        select id, sc, row_number() over(partition by id order by sc desc) rn from (
            select id, sc FROM your_table
            UNPIVOT (sc for col in (Score1, Score2, Score3, Score4, Score5))
        ) tt
    )ttt
    where rn <= 2
    GROUP BY id
) h
ON your_table.id =  h.id

- Oto Shavadze

---

在我看来，这个问题使用正则表达式有些过度了。 - Vamsi Prabhala

@vkp - 当然可以使用instr和substring来完成相同的操作，只是正则表达式是我脑海中出现的最快的东西。 - Oto Shavadze

Answer 3

这个解决方案避免了 unpivot，只需要大量的剪切&粘贴&修改：

SELECT 
   dt.*,
   CASE maxscore -- GREATEST on all but the highest value,
                 -- simplified to a "Valued Case" based on mathguy's comment
      WHEN score1 THEN Greatest(score2, score3, score4, score5)
      WHEN score2 THEN Greatest(score1, score3, score4, score5)
      WHEN score3 THEN Greatest(score1, score2, score4, score5)
      WHEN score4 THEN Greatest(score1, score2, score3, score5)
      ELSE Greatest(score1, score2, score3, score4)
   END
FROM
 (
   SELECT t.*,
      Greatest(Score1,Score2,Score3,Score4,Score5) AS maxscore
   FROM tab t
 ) dt

基于 @vkp 和 @mathguy 的评论，不使用派生表/内联视图：

SELECT 
   t.*,
   Greatest(Score1,Score2,Score3,Score4,Score5) as Highest1_value,
   CASE Greatest(Score1,Score2,Score3,Score4,Score5)-- GREATEST on all but the highest value
      WHEN score1 THEN Greatest(       score2,score3,score4,score5)
      WHEN score2 THEN Greatest(score1,       score3,score4,score5)
      WHEN score3 THEN Greatest(score1,score2,       score4,score5)
      WHEN score4 THEN Greatest(score1,score2,score3,       score5)
      ELSE             Greatest(       score1,score2,score3,score4)
   END as Highest2_value
FROM tab t

- dnoeth

---

为什么要进行“maxscore”计算呢？你本可以将其包含在“case”表达式中。 - Vamsi Prabhala

这可以进一步简化。将case表达式写成这样：case greatest(score1,...,score5) when score1 then ... when score2 then...（将其转换为“简单”case表达式，而不是“搜索”case表达式；您无需重复外部的greatest(...)，因为它将在“左侧”）。 - user5683823