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

注意把NULL值转换成0 

with
    temp as (
        select
            t1.uid
        from
            exam_record t1
            left join examination_info t2 on t1.exam_id = t2.exam_id
            left join user_info t3 on t1.uid = t3.uid
        where
            tag = "SQL"
            and difficulty = "hard"
            and level = 7
        group by
            t1.uid
        having
            avg(score) > 80
    )
select a.uid uid,ifnull(a.exam_cnt,0) exam_cnt, ifnull(b.question_cnt,0) question_cnt from
(
select
    uid,
    count(submit_time) exam_cnt
from
    exam_record
where uid in (select * from temp)
and year(submit_time) = 2021
group by uid 
)a
left join
(select uid,count(submit_time) question_cnt
from practice_record
where uid in (select * from temp)
and year(submit_time) = 2021
group by uid )b 
on a.uid = b.uid
order by exam_cnt asc,question_cnt desc