牛客题霸-SQL进阶篇（刷题记录二）

本文主要是介绍牛客题霸-SQL进阶篇（刷题记录二），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本文基于前段时间学习总结的 MySQL 相关的查询语法，在牛客网找了相应的 MySQL 题目进行练习，以便加强对于 MySQL 查询语法的理解和应用。

由于涉及到的数据库表较多，因此本文不再展示，只提供 MySQL 代码与示例输出。

部分题目因为较难，附上题目解法讨论的链接供大家参考。

SQL 题目

SQL 136：查询每类试卷得分的前 3 名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择 uid 大者

select tag as tid, uid, ranking
from(select tag, uid,rank() over(partition by tag order by max(score) desc, min(score) desc, uid desc) as rankingfrom examination_info eijoin exam_record eron ei.exam_id = er.exam_idgroup by tag, uid
) b
where ranking <= 3

SQL 139：查询每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户 ID 降序排列

select uid, count(score) as exam_complete_cnt
from(select *,dense_rank() over(partition by uid order by month(start_time) desc) as date_rankfrom exam_record
) b
where date_rank <= 3
group by uid
having count(score) = count(uid)
order by exam_complete_cnt desc, uid desc

SQL 143：查询每份试卷每月作答数和截止当月的作答总数

select exam_id, date_format(start_time, '%Y%m') as start_month,
count(start_time) as month_cnt,
sum(count(start_time)) over(partition by exam_id order by date_format(start_time, '%Y%m')) as cum_exam_cnt
from exam_record
group by exam_id, start_month

SQL 145：查询未完成状态的试卷的未完成数 incomplete_cnt 和未完成率 incomplete_rate

select exam_id, (count(exam_id)-count(score)) as incomplete_cnt,
round((count(exam_id)-count(score))/count(exam_id), 3) as complete_rate
from exam_record
group by exam_id
having incomplete_cnt >= 1

SQL 146：查询每个 0 级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和 0 分处理

select ui.uid,
round(avg(if(score is null, 0, score)),0) as avg_score, 
round(avg(if(submit_time is null, duration, timestampdiff(minute, start_time, submit_time))), 1) as avg_time_took
from user_info ui
join exam_record er
on ui.uid = er.uid
join examination_info ei
on er.exam_id = ei.exam_id
where level = 0 and difficulty = 'hard'
group by uid

SQL 147：查询昵称以 ‘牛客’ 开头 ‘号’ 结尾、成就值在 1200~2500 之间，且最近一次活跃（答题或作答试卷）在 2021 年 9 月的用户信息

select ui.uid,nick_name,achievement
from user_info as ui
left join practice_record pr
on ui.uid = pr.uid
left join exam_record er
on ui.uid = er.uid
where nick_name like '牛客%号' 
and achievement between 1200 and 2500
group by ui.uid
having date_format(max(er.start_time),'%Y%m')=202109 
or date_format(max(pr.submit_time),'%Y%m')=202109

SQL 150：试卷得分按分界点 [90, 75, 60] 分为优良中差四个得分等级（分界点划分到左区间），查询不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出，结果按用户等级降序、占比降序排序）（难点：窗口函数 + case when）

select *,
round(count(*)/sum(count(*)) over(partition by level), 3) as ratio
from (select level,casewhen score < 60 then '差'when score >= 60 and score < 75 then '中'when score >= 75 and score < 90 then '良'when score >= 90 then '优'end as score_gradefrom user_info uijoin exam_record eron ui.uid = er.uid
) a
where score_grade is not null
group by level, score_grade
order by level desc, ratio desc# 极简洁版本：仅窗口函数
select level,
case when score >= 90 then '优'
when score >= 75 then '良'
when score >= 60 then '中'
else '差' end as score_grade,
round(count(*)/sum(count(*)) over(partition by level), 3) as ratio
from exam_record
left join user_info using(uid)
where score is not null
group by level, score_grade
order by level desc, ratio desc

链接：SQL 150 题目解法讨论

SQL 151：查询注册时间最早的 3 个人

# 方法一
select uid, nick_name, register_time
from user_info
order by register_time
limit 3# 方法二：窗口函数
select uid, nick_name, register_time
from(select uid, nick_name, register_time,rank() over(order by register_time) as rankingfrom user_info
) a
where ranking <= 3

SQL 152：查询求职方向为算法工程师，且注册当天就完成了算法类试卷的人，按参加过的所有考试最高得分排名。采用分页展示，每页 3 条，需要取出第 3 页（页码从 1 开始）的人的信息

select ui.uid, level, register_time, max(score) as max_score
from user_info ui
join exam_record er
on ui.uid = er.uid
join examination_info ei
on er.exam_id = ei.exam_id
where ui.job = '算法' and ei.tag = '算法'
and date_format(register_time, '%Y%m') = date_format(submit_time, '%Y%m')
group by ui.uid, level
order by max_score desc
limit 6, 3

这篇关于牛客题霸-SQL进阶篇（刷题记录二）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---