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

#写的有点复杂了，相同的条件可以并一下
select a.uid,a.exam_cnt,ifnull(b.question_cnt,0)
from
(select uid,count(submit_time) as exam_cnt
from exam_record
where uid in
(select a.uid
from exam_record a
left join user_info b
on a.uid = b.uid
left join examination_info c
on a.exam_id = c.exam_id
where c.tag = 'SQL' and c.difficulty = 'hard'
and b.level = 7
group by a.uid
having avg(a.score)> 80)
and date_format(submit_time,'%Y') = 2021
group by uid) a
left join
(select uid,count(submit_time) as question_cnt
from practice_record
where uid in
(select a.uid
from exam_record a
left join user_info b
on a.uid = b.uid
left join examination_info c
on a.exam_id = c.exam_id
where c.tag = 'SQL' and c.difficulty = 'hard'
and b.level = 7
group by a.uid
having avg(a.score)> 80)
and date_format(submit_time,'%Y') = 2021
group by uid) b
on a.uid = b.uid
order by exam_cnt asc,question_cnt desc;