SQL实战 10.高频SQL面试题 考试分数系列
SQL面试题考试分数系列（一）
描述
牛客每次考试完，都会有一个成绩表(grade)，如下:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了JS岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入):
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)
SQL如下
select job ,avg from ( select job,round(avg(score),3) as avg from grade group by job ) a order by avg desc
SQL面试题考试分数系列（二）
描述
牛客每次考试完，都会有一个成绩表(grade)，如下:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)
SQL如下
select b.id,b.job,b.score from (select job ,avg from ( select job,round(avg(score),3) as avg from grade group by job ) a order by avg desc)a, grade b where a.job=b.job and a.avg<b.score order by b.id
SQL面试题考试分数系列（三）
描述
第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000，
....
第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000，
不同的语言岗位(language)表简化如下:
请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:
SQL如下
select id,name,score from ( select a.id,a.language_id,a.score, dense_rank() over(partition by a.language_id order by a.score desc) as rn ,b.name from grade a, language b where a.language_id=b.id ) a where rn<=2 order by name,score desc
SQL面试题考试分数系列（四）
描述
牛客每次考试完，都会有一个成绩表(grade)，如下:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分
请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下:
解释:
第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的10000是中位数)
第2行表示Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]是正确的(即需要知道位置为1的12000与位置为2的13000才能计算出中位数为12500)
第3行表示前端岗位的中位数位置范围为[2,2]，也就是2。因为B语言岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的11000是中位数)
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when ...then ...else ..end函数)
select a.job, round(count(a.id)/2), round((count(a.id)+1)/2) from grade a group by a.job order by job
SQL面试题考试分数系列（五）
描述
牛客每次考试完，都会有一个成绩表(grade)，如下:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:
解释：
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when ...then ...else ..end函数，sqlite不支持自定义变量)
SQL如下
select distinct b.id,b.job,b.score,b.rn from (select a.job, round(count(a.id)/2) as start from grade a group by a.job union all select a.job, round((count(a.id)+1)/2) as start from grade a group by a.job ) a left join ( select id ,job ,score , row_number() over(partition by job order by score desc) as rn from grade ) b on a.job=b.job and a.start =b.rn order by id
