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

select
a.uid,
exam_cnt,
if(question_cnt is null,0,question_cnt)

from
(select
uid,
count(score) exam_cnt
from
exam_record
where
year(submit_time)='2021' and
uid in (select
exam_record.uid
from
exam_record left join user_info
on exam_record.uid = user_info.uid
where 
exam_id in 
(select exam_id from examination_info where tag='SQL' and difficulty='hard'
) and level=7
group by exam_record.uid
having avg(score)>80)
group by uid ) a
left join (select
uid,
count(score) question_cnt
from
practice_record
where
year(submit_time)='2021' and
uid in (select
exam_record.uid
from
exam_record left join user_info
on exam_record.uid = user_info.uid
where 
exam_id in 
(select exam_id from examination_info where tag='SQL' and difficulty='hard'
) and level=7
group by exam_record.uid
having avg(score)>80)
group by uid ) b
on a.uid=b.uid
order by exam_cnt ,question_cnt desc