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

SELECT t1.uid,count(distinct er.id) as exam_cnt,count(distinct p.id)as question_cnt
from
(
select u.uid
from user_info u
    left join exam_record er on u.uid=er.uid
    join examination_info ei 
    on er.exam_id=ei.exam_id and tag='SQL'and difficulty='hard'
    where u.level=7
    group by u.uid
    having avg(score)>80
)t1
JOIN exam_record er 
on er.uid = t1.uid and YEAR(er.submit_time)=2021
left join practice_record p
on p.uid=t1.uid and YEAR(p.submit_time)=2021
group by t1.uid
order by exam_cnt,question_cnt DESC

SELECT t1.uid,count(distinct er.id) as exam_cnt,count(distinct p.id)as question_cnt
from
(
select u.uid
from user_info u
    left join exam_record er on u.uid=er.uid
    join examination_info ei
    on er.exam_id=ei.exam_id and tag='SQL'and difficulty='hard'
    where u.level=7
    group by u.uid
    having avg(score)>80
)t1
JOIN exam_record er
on er.uid = t1.uid and YEAR(er.submit_time)=2021
left join practice_record p
on p.uid=t1.uid and YEAR(p.submit_time)=2021
group by t1.uid
order by exam_cnt,question_cnt DESC