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

select er.uid,count(distinct er.exam_id) as exam_cnt,
count(distinct pr.submit_time) as question_cnt
#计数提交时间，就避免了表连接时的重复性
from exam_record as er
left join examination_info as ei using(exam_id)
left join user_info as ui 
on er.uid=ui.uid and year(er.submit_time)='2021'
left join practice_record as pr 
on er.uid=pr.uid and year(pr.submit_time)='2021'
#在连接表时限制2021
where ui.level=7
and er.uid in 
(select exam_record.uid from exam_record left join examination_info using(exam_id)
 where tag='SQL' and difficulty='hard'
 group by exam_record.uid
 having avg(score)>80
)
#该语句是为了将SQL平均分数超过80的uid筛选出来
group by er.uid
order by exam_cnt asc,question_cnt desc