阿里云 建设网站怎么样,百度推广一级代理商名单,管家婆免费仓库管理软件,国家公信网查询系统本文基于前段时间学习总结的 MySQL 相关的查询语法#xff0c;在牛客网找了相应的 MySQL 题目进行练习#xff0c;以便加强对于 MySQL 查询语法的理解和应用。
由于涉及到的数据库表较多#xff0c;因此本文不再展示#xff0c;只提供 MySQL 代码与示例输出。
部分题目因…本文基于前段时间学习总结的 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 3SQL 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 descSQL 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_monthSQL 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 1SQL 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 uidSQL 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)202109SQL 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 3SQL 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
