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

题目寥寥几行字，信息量太大了。

select exam_record.uid uid,count(distinct if(year(exam_record.submit_time)=2021,concat(exam_id,exam_record.submit_time),null)) exam_cnt,count(distinct if(year(practice_record.submit_time)=2021,concat(question_id,practice_record.id),null)) question_cnt
from
practice_record right join exam_record on exam_record.uid=practice_record.uid
where exam_record.uid in (select exam_record.uid
from exam_record left join user_info on exam_record.uid= user_info.uid left join examination_info on examination_info.exam_id=exam_record.exam_id
where level=7 and exam_record.submit_time is not null and year(exam_record.start_time)=2021 and difficulty ='hard' and tag = 'sql'
group by exam_record.uid
having  avg(score)>80)
group by exam_record.uid
order by exam_cnt,question_cnt desc