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

# 分别统计两个记录表t1,t2,然后通过t0左连接t1,t2筛选红名大佬
select t0.uid
,ifnull(t1.exam_cnt,0) as exam_cnt
,ifnull(t2.question_cnt,0) as question_cnt
from 
(select exam_record.uid as uid
from exam_record 
join user_info using(uid)
join examination_info using(exam_id)
where difficulty="hard"
and tag="SQL"
and level=7
and submit_time like "2021%"
group by user_info.uid
having avg(score)>80) as t0
left join
(select uid
,count(score) as exam_cnt
from exam_record 
where submit_time like "2021%"
group by uid) as t1 using(uid)
left join 
(select uid
,count(score) as question_cnt
from practice_record 
where submit_time like "2021%"
group by uid) as t2 using(uid)

order by exam_cnt,question_cnt desc