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

select uid,exam_cnt,ifnull(question_cnt,0) from
(select uid,
count(exam_record.submit_time) as exam_cnt
from exam_record
where uid in(
select uid
from user_info
join exam_record using(uid)
join examination_info using(exam_id)
where tag='SQL' and level =7
group by uid
having avg(score)>80)
and year(exam_record.submit_time)=2021 
group by uid ) as ex
left join

(select uid,
count(score)as question_cnt from
practice_record
where uid in(
select uid
from user_info
join exam_record using(uid)
join examination_info using(exam_id)
where tag='SQL' and level =7
group by uid
having avg(score)>80) and year(submit_time)=2021
group by uid)as qu
using (uid)

order by exam_cnt,question_cnt desc