SQL1【简单】

牛客每次考试完，都会有一个成绩表(grade)，如下:idjobscore

1C++11001

第1行表示用户id为1的用户选择了C++岗位并且考了11001分请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入)jobavg

Java12500.000

考点：group by

select job, round(avg(score),3) as avg

from grade

group by job

order by avg desc

SQL2【中等】

牛客每次考试完，都会有一个成绩表(grade)，如下:idjobscore

1C++11001

第1行表示用户id为1的用户选择了C++岗位并且考了11001分请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序

如下：idjobscore

1C++11001

思路：先求得每个岗位的平均分数，再用内联接匹配每个id所在岗位的平均分数，取分数高于所被分配的平均分的id即可

select g1.id, g1.job, g1.score from grade g1

join (select job, avg(score) as avg from grade group by job) as g2

on g1.job=g2.job and g1.score>g2.avg

group by id

SQL3【较难】

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

1112000

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

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

得到结果如下:idnamescore

2C++13000

思路一：

用窗口函数分别给每个岗位下id的分数排序，作为筛选条件

select g.id, name, score

from (select *, dense_rank() over (partition by language_id order by score desc) as r

from grade) as g

join language l on l.id=g.language_id

where r<=2

order by name, score desc, g.id

思路二：自相关子查询

select g.id, name, score

from grade g

join language l on l.id=g.language_id

where (select count(distinct g1.score)

from grade g1

where g1.score>=g.score and g1.language_id=g.language_id) <=2

order by name, score desc, g.id

注意要使用dense_rank()，保证同一分数的排名相同，且一定有第二名(使用rank()若有两个第一则第三个人为第三名)；思路二中要计算distinct的score，也是同理。

SQL4【较难】

牛客每次考试完，都会有一个成绩表(grade)，如下:idjobscore

1C++11001请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序

得到结果如下:jobstartend

C++22

第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的

Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]是正确的

考点：使用公式

用窗口函数分别给每个岗位下id的分数排序，作为筛选条件

select job,

floor((count(*)+1)/2) as 'start',

floor((count(*)+1)/2)+if(count(*)%2=1,0,1) as 'end'

from grade

group by job

order by job

SQL5【困难】

牛客每次考试完，都会有一个成绩表(grade)，如下:idjobscore

1C++11001请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

得到结果如下:idjobscorerank

2C++10002

第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2

思路：给同一岗位的分数排序，正序和倒序，中位数的这两个顺序差不超过1；注意整数相减产生负数会报错，所以需要用cast(变量 as signed)

select id, job, score, r1 as 'rank'

from (

select id, job, score,

row_number() over (partition by job order by score desc) as r1,

row_number() over (partition by job order by score) as r2

from grade

) g

where abs(cast(r1 as signed)-cast(r2 as signed))<=1

order by id

注意：不要直接命名为rank而是要加引号，否则会报错