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

/*
select language_id, score, DENSE_RANK() over (order by score) as s
from grade
group by language_id;
*/
select score_rank.id, name, score_rank.score
from 
    (select *, dense_RANK() over (partition by language_id order by score desc) as s
    from grade
    ) as score_rank,
    language
where language.id = score_rank.language_id
and score_rank.s in (1,2)
order by name, score_rank.score desc, score_rank.id;

这道题的知识点还是挺多的。

第一点，如何根据分组来对某一列进行排名：

dense_rank() over(partition by group_column order by rank_column)

第二点，窗口函数不同与聚类函数，查询语句中可以包含所有所有列；

第三点就是如何根据多列进行排序。