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

select grade.id,name,score
from grade
left join `language` on `language`.id = grade.language_id
WHERE score >= (
    select max(g1.score)
    from grade g1
    where (g1.score <> (
        SELECT MAX(score)
        from grade g2
        WHERE g2.language_id = g1.language_id
        group by g2.language_id
    ) OR (
        SELECT COUNT(score)
        from grade g2
        WHERE g2.language_id = g1.language_id
        group by g2.language_id
    ) = 1)
    and g1.language_id = grade.language_id
    group by g1.language_id
)
ORDER by name asc,score desc