牛客SQL练习第65题

题目描述

牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如C++工程师，JAVA工程师，Python工程师，每个用户笔试完有不同的分数，现在有一个分数(grade)表简化如下:

第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000，
…
第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000，

不同的语言岗位(language)表简化如下:

请你找出每个岗位分数排名前2的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:

解答：

一般来说，我们无法获得一个表里面数据的排名，除非使用函数或者使用select 连接自己的表去比较。

连接自己的写法：
最关键的就是查询自己排名的写法

select g1.id, l.name, g1.score
from grade g1 join language l on g1.language_id=l.id 
where 
    (select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.language_id=g2.language_id 
    )  <=2 
order by l.name,g1.score desc ,g1.id;

使用dense_rank()函数的写法：

select g.id, l.name, g.score
from (select *, 
      dense_rank() over(partition by language_id order by score desc) as rank 
      from grade) g, 
      language l
where g.language_id = l.id and g.rank <= 2
order by l.name asc, g.score desc, g.id asc