html5网站开发案例视频,计算机网络技术电商网站建设与运营方向,网站开发软硬件,营销推广公司案例本文基于前段时间学习总结的 MySQL 相关的查询语法#xff0c;在牛客网找了相应的 MySQL 题目进行练习#xff0c;以便加强对于 MySQL 查询语法的理解和应用。
由于涉及到的数据库表较多#xff0c;因此本文不再展示#xff0c;只提供 MySQL 代码与示例输出。
部分题目因…本文基于前段时间学习总结的 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) 2021SQL 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, difficultySQL 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_recordSQL 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 erjoin examination_info eion er.exam_id ei.exam_idwhere tag SQL
) and tag SQL
order by min_score_over_avg
limit 1SQL 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 monthSQL 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链接 1MySQL 中 with rollup 的用法
链接 2SQL 127 题目解法讨论
SQL 128查询 2021 年每个未完成试卷作答数大于 1 的有效用户的用户 ID、未完成试卷作答数、完成试卷作答数、作答过的试卷 tag 集合按未完成试卷数量由多到少排序有效用户指完成试卷作答数至少为 1 且未完成数小于 5难点group_concat 用法
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链接 1MySQL 中的 group_concat 函数
链接 2SQL 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_recordgroup by uidhaving count(submit_time) / count(distinct date_format(submit_time, %Y%m)) 3
)
group by tag
order by tag_cnt descSQL 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 ascSQL 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 descSQL 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
