题目复盘——多表查询
题源牛客网——记录一下自己的学习
SQL20 月均完成试卷数不小于3的用户爱作答的类别
问题分析
(1)月均完成试卷数————总完成次数/完成时所在月份数量(有争议)>=3;
(2)完成时所在月份数————count(distinct date_format(submit_time,’%Y%m’));
(3)总完成次数————count(submit_time);
(4)作答次数————count(start_time);
(5)爱作答类别————group by tag;left join examination_info;
(6)排序————order by;
答案重写
SELECT tag,COUNT(start_time) tag_cnt FROM exam_record e_r
LEFT JOIN examination_info e_i ON e_r.exam_id=e_i.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;
细节问题
这个题对于其中条件的解读有点争议,尤其是那个月均,还有当月之类的含糊词语,容易理解错。
SQL21 试卷发布当天作答人数和平均分
问题分析
(1)5级以上用户————where level >5;
(2)试卷发放当天作答人数————count(distinct uid);date_format(release_time,’%Y%m%d’)=date_format(submit_time,’%Y%m%d’);
(3)平均分————sum()/count();avg();
(4)保留一位小数————round(,1);
答案重写
SELECT exam_id,count(distinct e_r.uid) uv,ROUND(avg(score),1) avg_score FROM exam_record e_r
LEFT JOIN user_info u_i ON u_i.uid=e_r.uid
LEFT JOIN examination_info e_i ON e_i.exam_id=e_r.exam_id
WHERE level>5 AND tag='SQL' AND DATE_FORMAT(e_r.release_time,'%Y%m%d')=DATE_FORMAT(submit_time,'%Y%m%d')
GROUP BY exam_id
ORDER BY uv DESC,avg_score ASC;
SQL22 作答试卷得分大于80的人的用户等级分布
问题分析
(1)SQL类别试卷得分大于80———tag=‘SQL’,score>80;
(2)等级分布————group by level;
(3)每个等级人数————count(distinct uid);
(4)数量降序————order by level_cnt desc;<