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

select er.uid as uid,count(distinct er.exam_id) as exam_cnt,count(distinct pr.submit_time,pr.question_id) as question_cnt from exam_record er left join practice_record pr on er.uid=pr.uid and year(er.submit_time)=2021 and year(pr.submit_time)=2021 where er.uid in( select er.uid from exam_record er left join examination_info ei on er.exam_id = ei.exam_id left join user_info ui on er.uid = ui.uid where tag='SQL' and difficulty='hard' and level = 7 group by er.uid having avg(score) > 80 ) and year(er.submit_time)=2021 group by er.uid order by exam_cnt,question_cnt desc 主要解决点在on后跟and on 后跟and 此时，筛选留下的结果是右表中year(submit_time)为2021的和左表中全部数据，右表中不满足year=2021的全部补为null；计算题目练习数用question_id和时间一起去重。