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

select user_info.uid uid,
count(distinct if(year(er.submit_time)=2021,er.id,null)) exam_cnt,
count(distinct if(year(pr.submit_time)=2021,pr.id,null)) question_cnt
from user_info
join exam_record er using(uid)
left join practice_record pr 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
    and 
    year(exam_record.submit_time)=2021 
    group by uid
    having avg(score)>80
    )
group by uid
order by exam_cnt asc,question_cnt desc