题解 | #满足条件的用户的试卷完成数和题目练习数#
满足条件的用户的试卷完成数和题目练习数
https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf
# select uid from user_info join exam_record using(uid) join examination_info using(exam_id)
# where level=7 and tag='SQL' and difficulty='hard'
# group by uid
# having avg(score)>80
# 找出了，SQL 高难度，均分>80的7级大佬
# count(er表score) exam_cnt left join count(pr表score) question_cnt
# 找出了，大佬们答卷数和做题数，保留答卷的全部大佬。完整如下：
select uid,exam_cnt,ifnull(qt,0) question_cnt
from (select uid,count(score) exam_cnt
from exam_record
where year(submit_time)=2021
group by uid
) t1 left join
(select uid,count(score) qt
from practice_record
where year(submit_time)=2021
group by uid
) t2 using(uid)
# 至此，找出了全部答卷者的答卷总数和练习总数
# 接下来过滤sql高难度80分+的7级大佬
where uid in(
select uid
from user_info join exam_record using(uid) join examination_info using(exam_id)
where level=7 and tag='SQL' and difficulty='hard'
group by uid
having avg(score)>80
)
order by exam_cnt,question_cnt desc
