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

select
    exam_record.uid,
    count(DISTINCT exam_record.exam_id) as exam_cnt,
    #使用distinct多列去重
# 错误：count(DISTINCT practice_record.question_id) as question_cnt
#     count(distinct if(year(practice_record.submit_time)=2021, practice_record.question_id,NULL)) as question_cnt
    count(DISTINCT practice_record.question_id, if(year(practice_record.submit_time)=2021,practice_record.submit_time,NULL)) as question_cnt
from exam_record
left join practice_record
on exam_record.uid = practice_record.uid
and year(exam_record.submit_time) = 2021
where exam_record.uid in (
    # 筛选满足条件的用户；注意join方式的选择。
    select b.uid
    from examination_info a
    left join exam_record b on a.exam_id=b.exam_id
    inner join user_info c on b.uid=c.uid and c.level=7
    where a.tag='SQL' and a.difficulty='hard'
    group by b.uid
    having avg(b.score) > 80
             )
and exam_record.submit_time IS NOT NULL
group by exam_record.uid
order by exam_cnt, question_cnt DESC;

# # 筛选满足条件的用户；注意join方式的选择，以下使用方法是错误的。
#     select er.uid
#     from exam_record as er
#     left join user_info as ui
#     on er.uid = ui.uid
#     and ui.level = 7
#     left join examination_info as ei
#     on ei.exam_id = er.exam_id
#     and ei.difficulty = 'hard'
#     and ei.tag = 'SQL'
#     group by er.uid
#     having avg(er.score) > 80