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

/*
核心信息：高难度，SQL，平均值大于80，7级；【2021年】 试卷总完成次数和题目总练习次数
1、先找出符合条件的uid，并将其作为子表，可以用with创建临时表，这样代码逻辑比较清晰
2、分别根据uid在exam_record和practice_record进行查询
3、将两个表的查询结果根据uid进行合并
4、对合并表进行总的查询
*/

with tmp as (
select uid
from exam_record left join examination_info using(exam_id) left join user_info using(uid)
where difficulty='hard'
    and tag='SQL'
    and level=7
    and year(submit_time)=2021
group by uid
having avg(score)>80
)

select uid, exam_cnt, if(question_cnt is null, 0, question_cnt)
from
(select uid, count(submit_time) as exam_cnt
from exam_record
where uid in (select uid from tmp) and year(submit_time)=2021
group by uid) t1

left join 

(select uid, count(submit_time) as question_cnt
from practice_record
where uid in (select uid from tmp) and year(submit_time)=2021
group by uid) t2 using(uid)

order by exam_cnt asc, question_cnt desc