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

with
    newidlist as (
        (
           select
user_info.uid as uid
from
user_info,
examination_info,
exam_record
where
user_info.uid = exam_record.uid
And examination_info.exam_id = exam_record.exam_id
AND examination_info.tag = 'SQL'
AND examination_info.difficulty = 'hard'
AND user_info.level = 7
group by
uid
having
avg(score) >=80
        )
    )
select
    newform2.uid as uid,
    exam_cnt,
    if(question_cnt is null,0,question_cnt)
from
    (
        (
            select
                uid,
                count(uid) as exam_cnt
            from
                exam_record
            where
                uid in (
                    select
                        *
                    from
                        newidlist
                )
            and year(submit_time) = 2021
            group by
                uid
        ) as newform2
        left join (
            select
                uid,
                count(question_id) as question_cnt
            from
                practice_record
            where
                uid in (
                    select
                        *
                    from
                        newidlist
                )
            AND  year(submit_time) = 2021
            group by
                uid
        ) as newform1 on newform2.uid = newform1.uid
    )
    order by exam_cnt asc,question_cnt desc

这道题不是很难，唯一的难点就是在于left join 因为practice_record查出的uid可能是不全的，需要用全的left join 不全的，然后把null改成0即可。