题解 | #满足条件的用户的试卷完成数和题目练习数#

#思路：
#①先用几次子查询把高难度SQL考试均分大于80的7级大佬选出来；
#②把各uid在21年完成考试次数求出；
#③把各uid在21年完成练题次数求出；
#④将②、③两表连接，加入筛选条件是uid在①选出的人员内即可。

select tb2.uid, a, if(b is null,0,b) question_cnt
from
(select uid, count(score) a
from exam_record 
where year(submit_time)=2021
group by uid) as tb2
left join 
(select uid, count(score) b
from practice_record 
where year(submit_time)=2021
group by uid
) as tb3
using(uid)
#下面的子查询是选出高难度SQL均分大于80的7级大佬
where uid in(
select uid 
from exam_record 
right join 
(select exam_id from examination_info where tag='SQL' and difficulty='hard') tb1
using(exam_id)
where uid in (
    select uid from user_info where level=7
)
group by uid
having avg(score)>80
)
order by a, question_cnt desc