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

# 子查询筛选红名大佬,左连接匹配
select uid
,count(score) as exam_cnt
,ifnull(t2.question_cnt,0)
from exam_record 
left join
(select uid
,count(score) as question_cnt
from practice_record 
where submit_time like "2021%"
group by uid) as t2 using(uid)

where uid in
(select exam_record.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)
and submit_time like "2021%"
group by uid
order by exam_cnt,question_cnt desc