题解 | #考试分数(三)#

开窗函数很方便，但底层逻辑和自连接类似，因此采用自连接实现。

select
    g.id,
    name,
    g.score
from
    grade g,
    (
        select
            g1.language_id,
            g1.score,
            count(distinct g2.score) c
        from
            grade g1,
            grade g2
        where
            g1.score <= g2.score
            and g1.language_id = g2.language_id
        group by
            g1.language_id,
            g1.score
    ) a,
    language l
where
    g.score = a.score
    and g.language_id = a.language_id
    and g.language_id = l.id
    and a.c <= 2
order by
    name,
    g.score desc,
    g.id