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

select ui.uid , count(distinct er.exam_id) exam_cnt,count(distinct pr.submit_time) question_cnt
from user_info ui join exam_record er using(uid)
join examination_info ei using(exam_id) 
left join practice_record pr using(uid)
where ui.uid in
(
select ui.uid uid
from user_info ui join exam_record er using(uid)
join examination_info ei using(exam_id)
where ei.tag="SQL" and ei.difficulty= "hard" and ui.level=7 
and exists (select * from exam_record where exam_record.uid=ui.uid and year(exam_record.submit_time)=2021)
group by ui.uid
having avg(er.score)>80
) and year(er.submit_time)=2021 and (year(pr.submit_time)=2021 or pr.submit_time is null)
group by ui.uid
order by exam_cnt asc,question_cnt desc