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

select uid, exam_cnt, case when question_cnt is null then 0 else question_cnt end from(select uid from exam_record
    left join examination_info using(exam_id)
    left join user_info using(uid)
    where tag='SQL' and difficulty = 'hard' and level = 7
    group by uid
    having avg(score)>80) 
as uid_cnt
left join (select uid,count(submit_time) as exam_cnt from exam_record
    where Year(submit_time) = 2021 and submit_time is not null
    group by uid
) as exam_cnt_list using(uid)
left join (select uid,count(submit_time) as question_cnt from practice_record
    where Year(submit_time) = 2021 and submit_time is not null
    group by uid) as practice_cnt_list using(uid)

order by exam_cnt, question_cnt desc