根据列分区选择每个人的两个最大值 [英] select two maximum values per person based on a column partition

问题描述

如果我有下表：

Person------Score-------Score_type
1           30          A
1           35          A
1           15          B
1           16          B
2           74          A
2           68          A
2           40          B
2           39          B

对于每个人和得分类型，我想挑选最大得分如下：

Where for each person and score type I want to pick out the maximum score to obtain a table like:

Person------Score-------Score_type
    1           35          A
    1           16          B
    2           74          A
    2           40          B

我可以使用多个select语句来执行此操作，但这将会很麻烦，特别是在以后。所以我想知道是否有一个功能可以帮助我这样做。我之前使用过parititon函数，但是只能在表中标记序列....

I can do this using multiple select statements, but this will be cumbersome, especially later on. so I was wondering if there is a function which can help me do this. I have used the parititon function before but only to label sequences in a table....

推荐答案

select person,
       score_type,
       max(score) as score
from scores
group by person, score_type
order by person, score_type;

使用分区功能我想你的意思是窗口功能。他们也可以这样使用：

With "partition function" I guess you mean window functions. They can indeed be used for this as well:

select person
       score_type, 
       score
from (
  select person, 
         score_type, 
         score,
         row_number() over (partition by person, score_type order by score desc) as rn
  from scores
) t 
where rn = 1
order by person, score_type;

这篇关于根据列分区选择每个人的两个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！