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

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

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

部分题目因为较难，难以独立做出或代码仅在平台上运行不成功，故附上题目解法讨论的链接供大家参考。

SQL 题目

SQL 110：在表中插入相关数据（一）

insert into exam_record(uid, exam_id, start_time, submit_time, score) 
values
(1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(1002, 9002, '2021-09-04 07:01:02', null, null)

SQL 111：在表中插入相关数据（二）

insert into exam_record_before_2021
select null, uid,exam_id, start_time, submit_time, score
from exam_record 
where year(submit_time) < 2021

SQL 112：在表中插入相关数据（三）

replace into examination_info (exam_id, tag, difficulty, duration, release_time) 
values (9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00')

SQL 123：查询所有用户完成 SQL 类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）

select tag, difficulty, round((sum(score)-max(score)-min(score))/(count(score)-2), 1) as clip_avg_score
from exam_record er
join examination_info ei
on er.exam_id = ei.exam_id
where tag = 'SQL' and difficulty = 'hard'
group by tag, difficulty

在这里插入图片描述

SQL 124：查询总作答次数 total_pv、试卷已完成作答数 complete_pv 和已完成的试卷数 complete_exam_cnt

select count(exam_id) as total_pv,
count(score) as complete_pv,
count(distinct if(submit_time is not null, exam_id, null)) as omplete_exam_cnt
from exam_record

在这里插入图片描述

SQL 125：查询 SQL 试卷得分不小于该类试卷平均得分的用户最低得分

select score as min_score_over_avg
from exam_record er
join examination_info ei
on er.exam_id = ei.exam_id
where score >= (
    select avg(score)
    from exam_record er
    join examination_info ei
    on er.exam_id = ei.exam_id
    where tag = 'SQL'
) and tag = 'SQL' 
order by min_score_over_avg
limit 1

在这里插入图片描述

SQL 126：查询 2021 年每个月里试卷作答区用户平均月活跃天数 avg_active_days 和月度活跃人数 mau

select date_format(submit_time, '%Y%m') as month,
round(count(distinct uid, date_format(submit_time,'%Y%m%d'))/count(distinct uid), 2) as avg_active_days,
count(distinct uid) as mau
from exam_record
where year(submit_time) = 2021
group by month

在这里插入图片描述

SQL 127：查询 2021 年每个月里用户的月总刷题数 month_q_cnt，日均刷题数 avg_day_q_cnt（按月份升序排序）以及该年的总体情况（难点：with rollup 用法简化代码）

select ifnull(date_format(submit_time, '%Y%m'), '2021汇总') as submit_month, 
count(question_id) as month_q_cnt,
round(count(question_id)/day(last_day(submit_time)),3) avg_day_q_cnt 
from practice_record
where year(submit_time) = 2021
group by date_format(submit_time, '%Y%m')
with rollup

在这里插入图片描述
链接 1：MySQL 中 with rollup 的用法

链接2：SQL 127 题目解法讨论

SQL 128：查询 2021 年每个未完成试卷作答数大于 1 的有效用户的用户 ID、未完成试卷作答数、完成试卷作答数、作答过的试卷 tag 集合，按未完成试卷数量由多到少排序（有效用户指完成试卷作答数至少为 1 且未完成数小于 5）（难点：group by 用法）

select uid, 
sum(case when submit_time is null then 1 else 0 end) as incomplete_cnt,
sum(case when submit_time is null then 0 else 1 end) as complete_cnt,
# 方法2
# sum(submit_time is null) as incomplete_cnt
# count(submit_time) as complete_cnt
group_concat(distinct date(start_time),':',tag separator ';') as detail
from exam_record er
join examination_info ei
on er.exam_id = ei.exam_id
where year(start_time) = 2021
group by uid
having complete_cnt >= 1 and incomplete_cnt > 1 and incomplete_cnt < 5
order by incomplete_cnt desc

在这里插入图片描述

链接 1：MySQL 中的 group_concat 函数

链接 2：SQL 128 题目解法讨论

SQL 129：查询当月均完成试卷数不小于 3 的用户们爱作答的类别及作答次数，按次数降序输出

select tag, count(tag) as tag_cnt
from exam_record er
join examination_info ei
on er.exam_id = ei.exam_id
where uid in(
    select uid from exam_record
    group by uid
    having count(submit_time) / count(distinct date_format(submit_time, "%Y%m")) >= 3
)
group by tag
order by tag_cnt desc

在这里插入图片描述

SQL 130：查询每张 SQL 类别试卷发布后，当天 5 级以上的用户作答的人数 uv 和平均分 avg_score，并按人数降序，相同人数的按平均分升序

select ei.exam_id, count(distinct ui.uid) as uv, round(avg(score), 1) as avg_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 level > 5 and tag = 'SQL'
group by ei.exam_id
order by uv desc, avg_score asc

在这里插入图片描述

SQL 131：查询作答 SQL 类别的试卷得分 > 80 的人的用户等级分布，并按数量降序排序（保证数量都不同）

select level, count(level) as level_cnt
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 tag = 'SQL' and score > 80
group by level
order by level_cnt desc

在这里插入图片描述

SQL 132：查询作答 SQL 类别的试卷得分 > 80 的人的用户等级分布，并按数量降序排序（注意：自建数据库时，以下代码能正确运行）

select exam_id as tid, count(distinct uid) as uv, count(start_time) as pv
from exam_record
group by exam_id
union
select question_id as tid, count(distinct uid) as uv, count(submit_time) as pv
from practice_record
group by question_id
order by uv desc, pv desc