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

select t0.uid,exam_cnt,ifnull(question_cnt,0) from
(
    select user_info.uid as uid,avg(score) from exam_record join   
    examination_info using(exam_id) 
    join user_info using(uid)
    where user_info.level=7 and difficulty='hard' and tag='SQL' group by user_info.uid  
    having avg(score)>80
) as t0
left join
(
    select uid ,count(*) as exam_cnt from exam_record 
    where  year(submit_time)=2021 group by uid 
) as t1
on t0.uid=t1.uid
left join 
(
    select uid,count(*) as question_cnt from practice_record 
    where  year(submit_time)=2021 group by uid
) as t2
on t1.uid=t2.uid
order by exam_cnt asc,question_cnt desc;