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

select
    t1.uid,
    count(distinct t2.id) as exam_cnt,
    count(distinct t3.id) as question_cnt
from
    (
        select
            ui.uid
        from
            exam_record er
            join user_info ui using (uid)
            join examination_info ei using (exam_id)
        where
            ei.tag = 'SQL'
            and ei.difficulty = 'hard'
            and year (er.submit_time) = '2021'
            and ui.level = 7
        group by
            ui.uid
        having
            avg(er.score) > 80
    ) t1
    left join exam_record t2 on t1.uid = t2.uid
    and year (t2.submit_time) = '2021'
    left join practice_record t3 on t1.uid = t3.uid
    and year (t3.submit_time) = '2021'
group by
    uid
order by
    exam_cnt asc,
    question_cnt desc

1.先通过条件查出 高难度SQL试卷得分平均值大于80并且是7级的红名大佬，并且 2021年有试卷完成记录的用户

2.连表查询 统计他们的2021年试卷总完成次数和题目总练习次数