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

一定要用left join函数不能用join函数，不然pr表取不到值的记录就没有了！

select er.uid,
exam_cnt,
if(question_cnt is null,0,question_cnt) 

from
 (select uid,
count(submit_time) exam_cnt
from exam_record 
where year(submit_time) =2021
group by uid) er

left join (
select uid,
count(submit_time) question_cnt
 from practice_record 
 where year(submit_time) =2021
 group by uid) pr
using(uid)

where uid in 
(select uid
from exam_record
join examination_info using(exam_id)
join user_info using(uid)
where difficulty="hard"
and tag="SQL"
and level=7
group by uid
having avg(score)>=80)


order by exam_cnt asc,question_cnt desc