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

# 查询高难度SQL试卷得分平均值大于80并且是7级的用户名id
select er.uid
from exam_record er
join examination_info ei
on er.exam_id=ei.exam_id
join user_info ur
on er.uid=ur.uid
where level = 7     # 条件筛选7级用户的高难度sql试卷作答记录
and tag = 'SQL'
and difficulty = 'hard'
group by er.uid        # 按用户id分组查询各人的SQL高难度试卷平均分，并过滤
having avg(score)>80    # 聚合函数自动忽略空值，故不用考虑未提交情况
# 统计以上id号用户的2021年试卷总完成次数和题目总练习次数并保留2021年试卷完成次数大于0的用户，按要求排序
select k1.uid, exam_cnt, if(question_cnt is null, 0, question_cnt)
from (
    select uid, count(*) exam_cnt
    from exam_record
    where year(submit_time) = '2021'
    group by uid
) k1
left join (
    select uid, count(*) question_cnt
    from practice_record
    where year(submit_time) = '2021'
    group by uid
) k2
on k1.uid=k2.uid
where k1.uid in (
    select er.uid
    from exam_record er
    join examination_info ei
    on er.exam_id=ei.exam_id
    join user_info ur
    on er.uid=ur.uid
    where level = 7     
    and tag = 'SQL'
    and difficulty = 'hard'
    group by er.uid        
    having avg(score)>80  
)
and exam_cnt>0
order by exam_cnt, question_cnt desc;