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

select exam_record.uid, count(distinct exam_record.exam_id) as exam_cnt, count(distinct t1.question_id,t1.submit_time) as question_cnt from exam_record left join (select * from practice_record where year(submit_time)=2021) t1 on exam_record.uid = t1.uid where exam_record.submit_time is not null and exam_record.uid in( 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 difficulty='hard' and user_info.level=7 and year(exam_record.submit_time)=2021 and year(exam_record.submit_time) is not NULL group by exam_record.uid having avg(score)>80) and year(exam_record.submit_time)=2021 group by exam_record.uid order by exam_cnt asc,question_cnt desc