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

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 u.uid
            from user_info u
            join exam_record e
            using (uid)
            join examination_info
            using(exam_id )
            where level=7 and tag='SQL'
            group by u.uid
            having avg(score)>80)
     and year(submit_time)=2021
group by uid) as t1
left join (
select uid,count(submit_time) as question_cnt
from practice_record
where uid in (select u.uid
            from user_info u
            join exam_record e
            using (uid)
            join examination_info
            using(exam_id )
            where level=7 and tag='SQL'
            group by u.uid
            having avg(score)>80)
     and year(submit_time)=2021
group by uid) as t2
using(uid)
order by exam_cnt ,question_cnt desc