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

# 先找到SQL类试卷得分平均值>80并且是7级的用户
with t1 as (
    select uid
    from exam_record
    where exam_id in (select exam_id from examination_info where tag = 'SQL')
    and score is not null
    and uid in (select uid from user_info where level = 7)
    group by uid
    having avg(score) > 80
),
t2 as (
# 统计他们在2021年试卷总完成次数和题目总练习数
    select uid, sum(if(score is null, 0, 1)) as exam_cnt
    from exam_record
    where uid in (select uid from t1)
    and year(start_time) = '2021'
    group by uid
)# 使用if处理没有练习试卷的情况
select t2.uid, t2.exam_cnt, if(a.question_cnt is null, 0, a.question_cnt) as question_id
from t2
left join (
    select uid, count(score) as question_cnt
    from practice_record
    where year(submit_time) = '2021'
    and uid in (select uid from t1)
    group by uid
) as a
on a.uid = t2.uid
order by t2.exam_cnt, question_cnt desc;