小笨蛋看过来| #满足条件的用户的试卷完成数和题目练习数#

# 高难度SQL试卷得分平均值大于80并且是7级的红名大佬（名单）
(select 
    ui.uid
from exam_record er 
join user_info ui on er.uid = ui.uid
join examination_info ei on er.exam_id = ei.exam_id
where tag = 'SQL' and difficulty = 'hard' and level = '7'
group by ui.uid
having avg(er.score) > 80)

# 最终结果
select a.uid, a.exam_cnt, ifnull(b.question_cnt,0)
from
(select 
    uid,
    count(score) exam_cnt
from exam_record where year(submit_time)=2021
group by uid)  a # 完成试卷数
left join 
(select 
    uid,
    count(score) question_cnt
from practice_record where year(submit_time)=2021
group by uid) b # 完成练习数
on a.uid = b.uid
where a.uid in
(
    select 
        ui.uid
    from exam_record er 
    left join user_info ui on er.uid = ui.uid
    left join examination_info ei on er.exam_id = ei.exam_id
    where tag = 'SQL' and difficulty = 'hard' and level = '7'
    group by ui.uid
    having avg(er.score) > 80
)
order by exam_cnt asc, question_cnt desc