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

select
uu.uid,
count(distinct a.id) as exam_cnt,
count(distinct b.id) as question_cnt
from (
select distinct t2.uid from user_info as t1 join exam_record as t2 using(uid) join examination_info as t3 using(exam_id) 
where t1.level = 7 
and t3.tag = 'SQL' 
and t3.difficulty = 'hard'
group by t1.uid having avg(t2.score) > 80 
) as uu left join exam_record as a on uu.uid = a.uid and year(a.submit_time) = 2021 left join practice_record as b on uu.uid = b.uid and year(b.submit_time) = 2021
group by uu.uid
order by exam_cnt asc,question_cnt desc