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

# 外层查询条件：高难度SQL试卷得分平均值大于80且是7级的用户
# 连接子查询1:2021年试卷总完成次数
# 连接子查询2:2021年题目总练习次数
# 保留2021年有试卷完成记录的用户：year(submit_time) = 2021，且count(submit_time)
# 排序
# 注意点：统计为空的时候，要变为0
select
    uid,
    if (exam_cnt is null, 0, exam_cnt) as exam_cnt,
    if (question_cnt is null, 0, question_cnt) as question_cnt
from
    (
        select
            uid,
            count(submit_time) as exam_cnt
        from
            exam_record
        where
            year (submit_time) = 2021
        group by
            uid
    ) as ta
    left join (
        select
            uid,
            count(submit_time) as question_cnt
        from
            practice_record
        where
            year (submit_time) = 2021
        group by
            uid
    ) as tb using (uid)
where
    uid in (
        select
            user_info.uid
        from
            exam_record
            join user_info using (uid)
            join examination_info using (exam_id)
        where
            difficulty = 'hard'
            and tag = 'SQL'
            and level = 7
        group by
            user_info.uid
        having
            avg(score) > 80
    )
order by
    exam_cnt,
    question_cnt desc