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

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


 from 
(
    select 
        uid,
        count(1) as exam_cnt 
        
    from exam_record
    where year(submit_time)=2021 and score is not null
    group by uid
)a
left join 
(
    select 
        uid,
        count(1) as question_cnt 
    from practice_record
    where year(submit_time)=2021 and score is not null
    group by uid
    
)b
on a.uid = b.uid
where a.uid in 
(
        select
        er.uid
    from examination_info ei
    inner join exam_record er
    on ei.exam_id = er.exam_id 
    inner join user_info ui 
    on ui.uid = er.uid 
    where ei.tag='SQL' 
        and ei.difficulty='hard'
        and ui.level=7 
        and year(er.start_time)=2021
        and er.submit_time is not null
    group by er.uid
    having(avg(er.score)>80)
)

order by exam_cnt,question_cnt desc