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

with temp_table as (
    select
        distinct t1.uid
    from
        user_info t1,
        examination_info t2,
        exam_record t3
    where
        t1.uid = t3.uid
        and t2.exam_id = t3.exam_id
        and t2.tag = 'SQL'
        and t2.difficulty = 'hard'
        and year(t3.submit_time) = 2021
        and t1.level = 7
    group by
        t1.uid
    having  
        avg(t3.score) > 80)

select 
    t_3.uid,
    exam_cnt,
    ifnull(question_cnt,0) as question_cnt
from
    (select 
        t3.uid,
        count(*) as exam_cnt
    from
        exam_record t3
    where
        year(t3.submit_time) = 2021
        and t3.uid in (select * from temp_table)
    group by
        t3.uid) t_3
left join
    (select 
        t4.uid,
        count(*) as question_cnt
    from
        practice_record t4
    where
        year(t4.submit_time) = 2021
        and t4.uid in (select * from temp_table)
    group by
        t4.uid) t_4
on
    t_3.uid = t_4.uid
order by
    exam_cnt,question_cnt desc