题解 | #满足条件的用户的试卷完成数和题目练习数#
满足条件的用户的试卷完成数和题目练习数
http://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf
其实看到这道题，我一开始满脑子在想，怎么结合在一起，因为条件基本顺从于exam_record这张表，结果写得非常复杂且不正确
后面我想了下，为啥不能先把这些数据表先做简化呢
于是第一步，我先从统计那步下手，分别将exam_record完成试卷数 和 practice_record题目练习数一一算出来
select uid, count(submit_time) as exam_cnt from exam_record where YEAR(submit_time) = 2021 group by uid
select uid, count(submit_time) as question_cnt from practice_record where YEAR(submit_time) = 2021 group by uid
第二步，我着重于它的筛选条件“高难度SQL试卷得分平均值大于80并且是7级的红名大佬”
这步其实针对于exam_record,user_info,examination_info这三张表，后两者只是用于连接作用，因此难度并不大
需要注意的是，该SQL只查询Uid是为了后续的衔接，加上其他字段并无太大实际作用
select uid from exam_record join examination_info using(exam_id) join user_info using(uid) where tag = 'SQL' and difficulty = 'hard' and `level` = 7 group by uid having avg(score) >= 80
第三步，前者条件也筛选出来了，后者试卷数和练习数也写出来了，只剩下组装这项工程了
试卷数和练习数的SQL语句里面留下uid这个字段，目的就是链接三者下面为最终SQL语句
select uid, exam_cnt, if(question_cnt is null, 0, question_cnt) from (select uid, count(submit_time) as exam_cnt from exam_record where YEAR(submit_time) = 2021 group by uid) t left join (select uid, count(submit_time) as question_cnt from practice_record where YEAR(submit_time) = 2021 group by uid) t2 using(uid) where uid in ( select uid from exam_record join examination_info using(exam_id) join user_info using(uid) where tag = 'SQL' and difficulty = 'hard' and `level` = 7 group by uid having avg(score) >= 80 ) order by exam_cnt asc, question_cnt desc
