题解 | #满足条件的用户的试卷完成数和题目练习数#
满足条件的用户的试卷完成数和题目练习数
https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf
select b1.uid,b1.exam_cnt,(case when b2.uid is null then 0 else b2.question_cnt end)
from
(select uid,count(submit_time) as exam_cnt
from exam_record
where uid in
##高难度SQL试卷得分平均值大于80并且是7级的红名大佬
(select exam_record.uid
from exam_record
left join examination_info on exam_record.exam_id=examination_info.exam_id
left join user_info on exam_record.uid=user_info.uid
where tag='SQL' and level=7 and difficulty='hard'
group by exam_record.uid
having avg(score)>80)
and year(start_time)='2021'
group by uid
having count(submit_time)>=1)
as b1
left join
(select uid,count(*) as question_cnt
from practice_record
where year(submit_time)='2021'
group by uid
) as b2
on b1.uid=b2.uid
order by b1.exam_cnt,b2.question_cnt desc
