row_number增加递增序号列

--按优先级leverl(1、2、3)，同记录只显示最高级别
with soure as (
    select 1 leverl, 1 id,'等级1' name,'数学' subject,80 grade from dual
    --union all 
    --select 1 leverl, 2 id,'等级2' name,'语文' subject,90 grade from dual
    --union all 
    --select 1 leverl, 3 id,'等级3' name,'英语' subject,100 grade from dual
    union all 
    select 2 leverl, 4 id,'等级1' name,'数学' subject,80 grade from dual
    union all 
    select 2 leverl, 5 id,'等级2' name,'语文' subject,90 grade from dual
    --union all 
    --select 2 leverl, 6 id,'等级3' name,'英语' subject,100 grade from dual
    union all 
    select 3 leverl, 7 id,'等级1' name,'数学' subject,80 grade from dual
    union all 
    select 3 leverl, 8 id,'等级2' name,'语文' subject,90 grade from dual
    union all 
    select 3 leverl, 9 id,'等级3' name,'英语' subject,100 grade from dual
)

select 
    a.leverl,a.id,a.name,a.subject,a.grade
from(    
    select 
        row_number() over(partition by a.name,a.subject order by a.leverl) rn,
        a.leverl,a.id,a.name,a.subject,a.grade
    from soure a
) a
where rn=1