题解 | #SQL类别高难度试卷得分的截断平均值#

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