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

select uid,exam_cnt,if(question_cnt is null,0,question_cnt)
from
# table 1
(SELECT uid,count(submit_time) as exam_cnt
from exam_record 
where year(submit_time)=2021
group by uid) as table1
left join 
#table 2
(select uid,count(submit_time) as question_cnt
from practice_record
where year(submit_time)=2021
group by uid) as table2
using(uid)
where uid in ( #挑选哪些uid符合条件
              select uid 
              from exam_record left join user_info using(uid)
              left join examination_info using(exam_id)
              where tag = 'SQL' and level=7
              group by uid
              having avg(score)>80
)
order by exam_cnt,question_cnt desc